Update Query Problem

P

Phil McF

Employer wants a new field (ClosedDate) added to table (QAK1Data) and back
filled from field (EnteredDate) in another table (BillingData) when field
(Closed) in table (QAK1Data) is True. Table QAK1Data hold information for
individual jobs and is indexed on the field JobNo. Table BillingData holds
information on the various jobs. There is a one to many relationship between
QAK1Data and BillingData on th e common field JobNo. I have tried various
queries including subqueries with no success. The following query gives an
error "You tried to execute a query that doesn't include the specified
expression 'ClosedDate' as part of an aggregate function".

UPDATE QAK1Data SET QAK1Data.ClosedDate =
Max([BillingData].[EnteredDate]) WHERE (((QAK1Data.Closed)=True));

Any sugguestions or help would be most welcome

Thanks

Phil
 
M

[MVP] S.Clark

You're trying to do this in one query, whereas I would do it in two, maybe
three.

Find the max values for all of the data in one query, then link that back to
the main data, and perform the update. This may require saving the maxval
data to a temp table, too.

Read this: http://www.mvps.org/access/queries/qry0020.htm

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
Top