Update query.

[jfaz]

I am going round in circles with this and would be grateful for any help
offered. It may be a case that I am missing something quite simple but
phrase wood and trees comes to mind!!!!!!!!!!!!!!!!!!!!!!!!!

I have a table TblJobTotals with the following fields.
Job Number, Trade (both these fields together make up the primary key),
Original hours, variation hours, completed hours original, completed hours
variation.

I need to populate this table with summary information from two additional
tables TblOriginal and TblVariation.

I have created queries over each of the tables grouping by job number and
trade and sum of hours.

I need to pass these summary totals to my TblJobTotals but am unsure how to
do it.

How can I create an update query which will drop the totals into the
relevant record based on job number and trade?

 

[John Spencer]

You can't do it directly and you really should not do it at all - except
in rare circumstances.

You should calculate these values as needed in a query and should not
even have the table TblJobTotals.

If you really, really, really HAVE to do this, then it is possible.

Are you updating EXISTING records or are you ADDING new records to
TblJobTotals?

Adding is fairly simple
- Build a select query using your two queries to show all the data you
want to import
-- Change the query to an Append query

UPDATING is much more complex since you cannot use the total (aggregate)
queries in an update query, but must use the aggregate functions DSum,
DLookup, etc. Alternatively, you can turn the two summary queries into
make table queries and then join the new tables to the tblJobTotals and
do an update from that.

And then you need to go through this process whenever a new record is
added, deleted, or modified in tblOriginal or tblVariation. In other
words, don't do this. Use a query to calculate the values when you need
them.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================