Search results for query: *

Greetings! I finally found the answer to this problem. The primary key on the SQL table is a bigint and apparently Access 2013 doesn't recognize bigints as numbers, so it's creating the index in the linked table as text. Check out this link for more info...

Hmm. Possibly. I will double check both that & the primary key when I'm in the office on Wednesday. Thanks for your help! Cheers, May

Yes. The primary key in the underlying table in the view is designated as the key in the Access linked table. Cheers, May

I have come across the most bizarre thing I've ever seen in decades of programming in Access and I'm totally stumped! I am using Access 2013 as a FE to a SQL Server 2012 BE. I have numerous linked tables in the FE to views on the BE and have created Access queries on those linked tables. The...

Thanks, Frederico! So given that I have to limit the data before the windowing, I can't put this into a view. I will try your code when I'm back in the office next week. Cheers, Rissa

I need to get the sum of the next 12 months worth of revenue from an amortization table and instead of getting one value per grouped row, I'm getting one row for each row in the table. The actual table is more complicated than this example and is pushing 23 million rows, but this is...

Thank you, one and all. I did a bit of tweaking and finally have a view that gives the state of things as of the end of the month. I still need to do a bit of testing around the dates, but it is very close. For anyone who is interested, here is the code I ended up with: ;with --Orders O as...

Thank you, gk53, but this isn't working. I'm getting a "Incorrect syntax near the keyword 'group'" error on the row_number() function. I pretty sure it's because the database is running on SQL Server 2005 (no SP). Any other ideas? Cheers, May

Frederico, That didn't work because ItemId does not exist in the Orders table. Perhaps this will clarify the situation: Shipments: ShipId (PK), ShipType, OrderId (FK to Orders), ShipDt, and other fields ShippedItems: ShipId (FK to Shipments), ItemId (FK to Items), CreateDt, and other fields...

Thanks, Frederico. I will try it when I'm back in the office on Monday. Cheers, May

BTW running SQL Server 2008 R2

I have a database that tracks inventory movement. The main tables are Orders, Shipments, ShippedItems and Items. The relationships are: a Shipment may or may not have an Order, Shipments will have 1 or more Items and Items can be attached to 1 or more Shipments. The ShippedItems table is a...

Thanks! Looks like that resolved the effect if not the underlying cause of the delay. Much appreciated! Cheers, Rissa

We are running Accpac 500 ERP V5.5a with SQL Server 2005. All users log on to the server via Remote Desktop Connection and run Accpac there. I have a macro which has been in production since Dec 2007 that ports A/R data from an external billing system to accpac and vice versa. Recently (April...

You're right, you can't refer to a computed column in another computed column. Apparently, they're an end product. Casting both values to decimal didn't work, so I simply called the same function in the NBV column which works beautifully. It means that there are two calls to the function...

I have created the function and tested it. And I have changed the AccumDepn field from money to a computed column that calls the function. That seems to be working just fine - the values in the computed column are correct. However, when I try to change the NBV field to a computed column using...

Thank you, Markros!! I was not aware of computed columns and this simplifies things in several other places as well. Kudos!

I know there is a simple answer to this, but I can't seem to get to it today. I have an Assets table that contains (among other things) the original cost, Accumulated depreciation, and net book value. I need to update the accumulated depreciation & NBV based on the sum of posted records in the...

LOL! That's pretty much what I figured, but had to gather my ammunition to nip it in the bud. Thank you!

I have a client moving from: ERP 5.4a running on Windows 2003 & MSSQL 2000 to: ERP 5.5a running on Windows 2008 & MSSQL 2005 on a completely new server. They also have several macros built with VBA using COMAPI. The users connect via Remote Desktop Connection & LANPak licences. The...