min/max values from one table to another

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.

Doug

re: min/max values from one table to another

Doug_Wilder@nps.gov (Wilder) wrote in
news:b2dc7eea.0309261028.6ec0089e@posting.google.c om:
[color=blue]
> 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.[/color]

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

re: min/max values from one table to another

Thanks, this works great! I did it first for the latest dates and now
I'm trying to get the earliest dates into the table but am having
difficulties. I reran the query to get the earliest dates instead of
the latest and then tried making an update query to add these to the
table. In the update query, I added the table and the query and
linked them on the common field. Then in the grid I added the field I
want to contain the values and set its "update to" value to
[qryDates].[earliest]. Here's the SQL:

UPDATE tblPacks INNER JOIN qryMinMaxDates ON tblPacks.PackID =
qryMinMaxDates.ASSO SET tblPacks.EarliestObservationDate =
[qryMinMaxDates].[earliest];

When I run this I get the error:
Operation must use an updateable query

So how do I add the earliest dates to the table? Thanks again for
your expert help!

Doug Wilder
National Park Service