Putting total of one table into another.

[ChrisM]

Hi,

I have an access table with 1000's of records, one field of which is a part
number and another field is a quantity.

There can many rows with the same part-number.

There are lots of operations that need the total quantity for all parts in
the above table, and calculating this on the fly each time I need it takes
too long.

My solution was to have another table that contains part-number and total
quantity which I can update periodically (the data doesn't change all that
rapidly) and use for the above operations.

So I thought somthing like:

UPDATE tempTable set tempTable.TotalQuantity =
(SELECT sum(quantity) from bigTable
WHERE bigTable.PartNumber = [pPartNumber]
)
WHERE tempTable.PartNumber = [pPartNumber]

would work to update the total quantity for a given product. However, I get
'Operation must use an updatable query'
Can anyone help or suggest an alternative. (I can't change the design of the
database, I can only add extra tables and queries)

Thanks,

ChrisM

 

[ChrisM]

OK, found a solution. Sorry I should have looked harder before my first
post.
For anyone else with the same problem, the following works:

UPDATE tempTable
SET tempTable.TotalQuantity
= DSum("quantity","bigTable","PartNumber='" & [pPartNumber] & "'")
WHERE tempTable.PartNumber = [pPartNumber]

Anyone like to comment on this, is there a better way?

ChrisM