UPDATE Query

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

I am having problems updating a table's records.

1. I have a large table with a lot of data that is
dumped into it. The records that I need to use are:


Table1:

total_hours
period_date
master
wrkpkg

2. I would like to update a field in a table called
hours which is the sum of Table.total_hours where
Table1.period_date = *specific date* AND master & wrkpkg
= the master & wrkpkg of the table that is going to be
updated. Can someone please tell me the easiest way to
do this. I keep getting an error that I need to use an
updatable query so would it be easier to create a table
then update it from there?


ANy suggestions would be greatly appreciated.

Chad
 
Access will not allow you to do this in a single query. It
MAY let you do it if you create a "sum" query first, and
then make the update query from the the new query and the
destination table (hours).
If this fails as well, you could either make the operation
two stage by filling a temp table with your summary data
and then use this in the update query, or write code to
update your hours table row by row.
 
You are probably using a Totals Query as the Sub-Query to
your Update Query. Since the Totals Query is not
updateable, JET implementation (wrongly) concludes the
(whole) Query is not updateable and errors out.

Use one of the Domain Aggregate Functions, e.g. DSUM()
instead of your SubQuery.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top