Sub-Select issues :(

[vapid2323]

Hey guys, this is the first time I have ever needed to make a sub-select query and my brain is having a hard time understanding what it is I am doing.

I am following a tutorial found HERE

As for my data I have three fields, fk_SupplierID, AuditStartDate and LeadAuditorAuditHistory

I need to get the Max(AuditStartDate) and the matching LeadAuditor for that date. I feel like I am really close but at this point I need some help.

This is my SQL:

SELECT Q.fk_SupplierID, Q.LeadAuditorAuditHistory
FROM tblAuditHistory AS Q INNER JOIN tblAuditHistory AS T ON Q.AuditHistoryID = T.AuditHistoryID
WHERE LastAuditDate = (SELECT Max(T.AuditStartDate) FROM tblAuditHistory AS T WHERE T.fk_SupplierID = Q.fk_SupplierID);

When I try to run the query I get the enter parameter dialog box for LastAuditDate, and I am not sure why.

 

[jzwp22]

Are you trying to find the most recent auditstartdate for each supplier in tblAuditHistory?

 

[vapid2323]

Are you trying to find the most recent auditstartdate for each supplier in tblAuditHistory?

Yes thats correct

 

[jzwp22]

I would create & save the aggregate query first


query name: qryMostRecentDateBySupplier
SELECT fk_supplierID, Max(auditstartdate) as MaxofAuditStartDate
FROM tblAuditHistory
GROUP BY fk_supplierID

Then create a second query that joins the above back to your audit history table and join by both the supplierID and date fields, bring in the other fields you want


SELECT tblAuditHistory.fk_SupplierID, tblAuditHistory.AuditStartDate, tblAuditHistory.LeadAuditorAuditHistory
FROM tblAuditHistory INNER JOIN qryMostRecentDateBySupplier ON (tblAuditHistory.AuditStartDate = qryMostRecentDateBySupplier.MaxOfAuditStartDate) AND (tblAuditHistory.fk_SupplierID = qryMostRecentDateBySupplier.fk_SupplierID);

If you wanted to do this using the nested query approach rather than the two query approach above, this is what the nested query would look like. You would have to include the subquery as part of the INNER JOIN

SELECT tblAuditHistory.fk_SupplierID, tblAuditHistory.AuditStartDate, tblAuditHistory.LeadAuditorAuditHistory
FROM tblAuditHistory INNER JOIN (SELECT tblAuditHistory.fk_SupplierID, Max(tblAuditHistory.AuditStartDate) AS MaxOfAuditStartDate
FROM tblAuditHistory
GROUP BY tblAuditHistory.fk_SupplierID) AS Q1 ON (tblAuditHistory.AuditStartDate = Q1.MaxOfAuditStartDate) AND (tblAuditHistory.fk_SupplierID = Q1.fk_SupplierID);

 

[vapid2323]

As far as I can tell that did the trick and is far more easy for my brain to follow lol.

Thanks so much for the help!

 

[jzwp22]

You're welcome. Glad I could help out.