IF it would work the SQL syntax for JET (the native database engine for
Access) would be
UPDATE tMFG INNER JOIN
( SELECT MFG_ID,
MIN(WDate) AS xStartDate,
MAX(WDate) AS xEndDate
FROM tSales
GROUP BY MFG_ID ) AS w ON tMFG.MFG_ID = w.MFG_ID
SET StartDate = w.xStartDate,
EndDate = w.xEndDate
Unfortunately, that will generate an error - "Must use updatable query"
Jet will not allow you to use an aggregate query in an update query.
You can do this one of two ways. Simplest is to use the VBA DMin and
DMax functions. This method is a bit slow, but with relatively small
datasets the performance is satisfactory.
UPDATE TMFG
SET StartDate = DMin("WDate","tSales","MFG_ID=" & Chr(34) & tMFG.MFG_ID
& Chr(34))
, EndDate = DMax("WDate","tSales","MFG_ID=" & Chr(34) & tMFG.MFG_ID &
Chr(34))
If MFG_ID is not a text field, but is a number field remove the two &
Chr(34).
With large datasets, you can create a temporary table based on the
subquery and then use that to update tMFG.
UPDATE tMFG INNER JOIN TempTable AS T
ON tMFG.MFG_ID = T.MFG_ID
SET StartDate = [T].[xStartDate],
EndDate = [T].[xEndDate]
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================