Update Query (v2000)

Topic

New Reply

I need a little confirmation. I have two tables and they need to remain separate. One is sales by county and one is sales by state. Each month the sales by county are loaded into a table with the following fields:
County, State, Month, Actual, Budget
The budget numbers are pre-loaded so all the user needs to do is load the Actual results. Once the actual #s are loaded to the county table, I need to have the data load automatically to the State table. The state table has the same layout as the county excluding the county field.
I tried an update query linking the state table to a query summing the counties by state but since it isn’t an updateable query, I can’t use that approach. I believe the best way to do this is use DLookup() in an update query to reference the query containing the state totals. If there’s a better way please show me the light.
Thanks!

Reply | Quote

Replies

(I won’t ask why you need the state totals in a table if they can be calculated in a query)

Your setup with DLookup should work, although it might be slow if the table is big.
As an alternative, you could change the totals query to a make-table query, run it, and use the resulting table in the update query, if it isn’t possible to use the result itself. If you wish, you can automate this with a few lines of code.

Reply | Quote

Just as I thought.
Thanks Hans!!

Reply | Quote

Just as I thought.
Thanks Hans!!

Reply | Quote

(I won’t ask why you need the state totals in a table if they can be calculated in a query)

Your setup with DLookup should work, although it might be slow if the table is big.
As an alternative, you could change the totals query to a make-table query, run it, and use the resulting table in the update query, if it isn’t possible to use the result itself. If you wish, you can automate this with a few lines of code.

Reply | Quote