Select Top 2 dates per SAP No

[primagic]

I have a table which has a list of SAP No's along with numerous other fields.

One of the fields is a date archived. So each SAP No will have a record potentially for each month. But some may have October and August but not September. Others will have January through to October.

I need to pull the last two dates (whether it be Oct & Sep or April & March) per SAP No.

My code at the moment:

SELECT FTEArchive.[SAP No], FTEArchive.DateArchived
FROM FTEArchive
WHERE (((FTEArchive.DateArchived) In (select top 2 [DateArchived] from FTEArchive where FTEArchive.[SAP No] = FTEArchive.[SAP No] order by FTEArchive.[DateArchived] desc)))
ORDER BY FTEArchive.DateArchived DESC;

I am only getting 1 record per SAP No and it is using the last date it can find in DateArchive field (for any field) and using this for each record

So like:

SAP No Date Archived
1234 31/10/2012
3455 31/10/2012
4567 31/10/2012


My objective is to get:

SAP No Date Archived
1234 31/10/2012
1234 30/09/2012
3455 31/10/2012
3455 30/09/2012
4567 31/10/2012
4567 30/09/2012

 

[dhookom]

Try alias the internal copy of FTEArchive:

SELECT FTEArchive.[SAP No], FTEArchive.DateArchived
FROM FTEArchive
WHERE DateArchived IN
(SELECT TOP 2 [DateArchived]
 FROM FTEArchive B
 WHERE B.[SAP No] = FTEArchive.[SAP No]
 ORDER BY B.[DateArchived] DESC)
ORDER BY FTEArchive.DateArchived DESC;

Duane
Hook'D on Access
MS Access MVP