Update column from query or other table

Hi

I'm creating a simple inventory system with the option to print reports for individual accounts or grand total for all accounts. The individual reports works just fine, but for the grand total-report I'd like to add the possibility to compare current numbers and numbers from previous dates.
So my idea was calculate the total each time I press the "Create report"-button and then copy these numbers into a table (a table which also keeps track of the date this data was added).

Now, I've got everything except the copy-process working. I've tried to copy the data with
But this gives me the error "Operation must use an updatable query. (Error 3073)". The above code works if I fill in a constant instead of the SELECT, so I'm guessing that's not ok to do.

So if anybody would have some tips or pointers of other ways to do this, I'd be very glad. If I'm thinking fundamentally wrong somewhere, please point it out, I'm still trying to learn and really appriciate all the advice I can get.

Oh, and I'm using Access 2003, and the above SQL is executed through a DoCmd.RunSQL in a OnClick-event.

re: Update column from query or other table

Hi Syd
I think I understand what you are trying to do but if I was to use an extra table just to hold the Totals I would delete everything out each time(Deleted * from Tablename;) then run an APPEND query to add the latest data in. This would add New Products as well as updated totals for previously existing products. I've run into the problem of what Access believes is an updatable dataset too.

However, I believe that a better solution would be to design a UNION query. This would have a section with a query summing the 'current' transactions and a second section summing the 'total' transactions. It may look something like this;-
Note that you must have the same number of columns returned by both queries and you arrange this by arranging a 'zero' column for 'TotalSales' in the 'Current' query (the first section) and a 'zero' column for 'CurrentSales' in the 'Total' query (second section).

You also need a way of inputting your dates. The usual way is to build a reference to a control on a form so the 'between bit may look more like this where the name of the input form is 'dialogSalesInPeriod';-
If you are summing literally all the values for the TotalSales section then you can dispense with the WHERE clause in the second section.

This query should give you two lines for each ProductID (only one line for NEW products) and can form the basis of the query for your Report where you can join it to Products table and the like to get Product Descriptions etc, then you must aggregate it to have only one line per ProductID.

S7