Do*********@nps.gov (Wilder) wrote in
news:b2**************************@posting.google.c om:
I'm trying to update a field in one table with the minimum
values of the field in another table. The two tables are
linked via a common field. I want to populate a date field in
one table with the earliest date in another table where the
linking fields values are equal. I set up a query with the
two tables, made sure the common fields were linked, set the
query as an "update" query, placed the field and table I want
to update in the query grid and set the 'update to' value to
"Min(firsttable.datefield)". When I run the query, I get the
following error:
"You tried to execute a query that does not include the
specified expression 'EarliestDate' as part of an aggregate
function"
Any ideas how to do this? Thanks.
you can use a query just like a table in building another query.
Build a proper aggregate query first, then build the update query
using the aggregate query and the second table.
aggregate Query:
SELECT firsttable.[linking field], min(firsttable.groupfield as md
from firsttable GROUP BY firsttable.[linking field]
The better way is to just create a select query using the second
table and the aggregate query because you really don't need the
duplicate field.
SELECT query
Select secondtable.[linking field], [aggregate query].md from
secondtable inner join [aggregate query] on secondtable.[linking
field] = [aggregate query].[linking field]
If you insist on having the field in secondtable, then convert the
select query above to an update query.
UPDATE secondtable inner join [aggregate query] on
secondtable.[linking field] = [aggregate query].[linking field] SET
mydatefield = [aggregate query].md