"M" is a name for an instance of Main Table. This way you can work with
multiple "copies" (instance) of the table and the SQL will know you want to
use the M instance (copy) or you want to use the Main Table instance (copy).
Since you want to get the TOP 3 DateReceived (I did have typo there in the sub
query) from the table for a specific part number you would normally use
something like the following to get the TOP 3 dates for a specific part
SELECT Top 3 [Main Table].DateReceived
FROM [Main Table]
WHERE [Main Table].PartNumber = "AB123"
ORDER BY [Main Table].DateReceived DESC
Using the correlated sub-query, You need a way to refer the PartNumber in the
main part of the query, but you couldn't use the following because you would
be checking the value of PartNumber against itself - which would basically get
you the overall TOP 3 dates and not the top 3 for each PartNumber
(SELECT TOP 3 [Main Table].DateReceived
FROM [Main Table]
WHERE [Main Table].PartNumber = [Main Table].PartNumber
ORDER BY [Main Table].DateReceived DESC)
So aliasing the table name in the subquery allows the comparison to get the
changing partNumber from the main query and compare that to the partnumber in
the subquery. IN other words get the PartNumber for the current record of
Main Table and use it in the comparison of the PartNumber in another instance
of the Main Table named M.
(SELECT TOP 3 [M].DateReceived
FROM [Main Table] as M
WHERE [M].PartNumber = [Main Table].PartNumber
ORDER BY [M].DateReceived DESC)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the help Mr Spencer, but what is the "M" referenced in the coee?
at first I thought it was an abbreviation at first, but I don't
follow..."FROM [Main Table] as M"
John Spencer said:
You can use a correlated sub-query in the WHERE clause.
Warning: Correlated sub-queries are slow because they run once for each record
in the recordset that is being examined. Lots of records to check = lots of
queries to run.
SELECT [Main Table].EntryNumber
, [Main Table].PartNumber
, [Main Table].NCRNumber
, [Main Table].DateReceived
, S.IMTRNR
FROM [Main Table] LEFT JOIN SIM92MFG_WINQUALSTS as S
ON [Main Table].PartNumber = S.IMPN
WHERE ((([Main Table].PartNumber) Is Not Null)
AND (([Main Table].NCRNumber) Is Null)
AND ((S.IMTRNR) Is Null Or
(S.IMTRNR)="nml"
AND [Main Table].DateReceived IN
(SELECT TOP 3 M.DateRecieved
FROM [Main Table] as M
WHERE M.PartNumber = [Main Table].PartNumber
AND M.NCRNumber is Null
ORDER BY M.DateReceived DESC)
ORDER BY [Main Table].PartNumber, [Main Table].DateReceived DESC;
You might need to expand the subquery to include the SIM92MFG_WINQUALSTS table
in a join and the criteria you are using in the main query.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Dirty70Bird wrote:
Hello, I am attempting to create a query where I would like the latest 3
receive dates for each part number. I have tried to use the top values
query, and started to look at a sub-query, but have a little trouble with the
syntax. Here is what I have so far:
SELECT [Main Table].EntryNumber, [Main Table].PartNumber, [Main
Table].NCRNumber, [Main Table].DateReceived, SIM92MFG_WINQUALSTS.IMTRNR
FROM [Main Table] LEFT JOIN SIM92MFG_WINQUALSTS ON [Main Table].PartNumber =
SIM92MFG_WINQUALSTS.IMPN
WHERE ((([Main Table].PartNumber) Is Not Null) AND (([Main Table].NCRNumber)
Is Null) AND ((SIM92MFG_WINQUALSTS.IMTRNR) Is Null Or
(SIM92MFG_WINQUALSTS.IMTRNR)="nml"
ORDER BY [Main Table].PartNumber, [Main Table].DateReceived DESC;
