Search code examples
ms-accessvbams-access-2007

Using the MAX() clause in Access 2007 SQL View

I am newer to Access and VBA, I'm coding in Access 2007 and attempting to build a query that will pull records from two different tables, the records in the tables have revision numbers assigned each time a revision is made. I can get the query to run and display the records, however I'm trying to pull "only" the most current revision of each record. I have tried using the MAX() clause written in SQL view for both the max revision number (I.E. rev1 rev2 rev3) as well as the most current date of the revisions (to pull the most recent date for the revisions). In both attempts Access is displaying all revisions for the records pulled. Am I not able to pull only the most current revision in Access? Below is the SQL code I've attempted to use,

SELECT MAX(tblNascoRevision.tbRevisionNum) AS tbRevisionNum, tblNasco.tbNCRNumber, tblNasco.tbGroupName, tblNasco.tbGroupNumber, tblNasco.tbAccountManager, tblNasco.tbSERNumber, tblNasco.cboNCRAnalyst, tblNasco.tbBAReceivedDate, tblNasco.tbBAAcceptedDate, tblNasco.tbRequestTitle, tblNasco.tbSubmittedToMDSS, tblNasco.tbImplementationDate,  tblNasco.tbRejectToSales1, tblNasco.tbReceiveFromSales1, tblNasco.tbRejectToSales2, tblNasco.tbReceiveFromSales2, tblNasco.tbComments, tblNascoRevision.tbSentToMDSS, tblNascoRevision.cboRevisionCodes

FROM tblNasco, tblNascoRevision

WHERE tblNasco.LocalID=tblNascoRevision.NascoLocalID

GROUP BY tblNasco.tbNCRNumber, tblNasco.tbGroupName, tblNasco.tbGroupNumber, tblNasco.tbAccountManager, tblNasco.tbSERNumber, tblNasco.cboNCRAnalyst, tblNasco.tbBAReceivedDate, tblNasco.tbBAAcceptedDate, tblNasco.tbRequestTitle, tblNasco.tbSubmittedToMDSS, tblNasco.tbImplementationDate, tblNasco.tbRejectToSales1, tblNasco.tbReceiveFromSales1, tblNasco.tbRejectToSales2, tblNasco.tbReceiveFromSales2, tblNasco.tbComments, tblNascoRevision.tbSentToMDSS, tblNascoRevision.tbRevisionNum

ORDER BY tblNasco.tbGroupName, tblNascoRevision.tbRevisionNum DESC;
Solution

  • I would solve it creating two queries:

    Query 1: get only max recordsets of tblNascoRevision

    SELECT tbRevisionNum, NascoLocalID, tbSentToMDSS, cboRevisionCodes
FROM tblNascoRevision rev,

 (SELECT MAX(tblNascoRevision.tbRevisionNum) AS tbRevisionNum, NascoLocalID
  FROM tblNascoRevision
  GROUP BY NascoLocalID) maxresults

 WHERE rev.tbRevisionNum = maxresults.tbRevisionNum
 AND rev.NascoLocalID= maxresults.NascoLocalID;

    Query 2: Join Query1 with tblNasco

    SELECT Query1.tbRevisionNum, tblNasco.tbNCRNumber, tblNasco.tbGroupName, tblNasco.tbGroupNumber, tblNasco.tbAccountManager, tblNasco.tbSERNumber, tblNasco.cboNCRAnalyst, tblNasco.tbBAReceivedDate, tblNasco.tbBAAcceptedDate, tblNasco.tbRequestTitle, tblNasco.tbSubmittedToMDSS, tblNasco.tbImplementationDate,  tblNasco.tbRejectToSales1, tblNasco.tbReceiveFromSales1, tblNasco.tbRejectToSales2, tblNasco.tbReceiveFromSales2, tblNasco.tbComments, Query1.tbSentToMDSS, Query1.cboRevisionCodes
FROM tblNasco, Query1
WHERE tblNasco.LocalID=Query1.NascoLocalID
ORDER BY tblNasco.tbGroupName, Query1.tbRevisionNum DESC;