Join on derived table

[benatom]

Can you join on a derived table in Access like you can in SQL Server?

How would I write this query in Access?

UPDATE tMFG
SET StartDate = w.xStartDate,
EndDate = w.xEndDate
FROM 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

 

[Stefan Hoffmann]

hi,

Can you join on a derived table in Access like you can in SQL Server?
How would I write this query in Access?

Subqueries in UPDATES normally don't work. I think you have to create a
separate query for the aggregates.


mfG
--> stefan <--

 

[John Spencer]

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
'====================================================