Nested query

[kpfunf]

I have the following two queries setup and working just fine. However, I know this is not the best way to write them, as this should be just one query, but I do not know how to nest these.

The idea is to find the serial numbers where the most recent assignment (max AssignmentDate) has a value in the ReturnDate field (that is, according to our data, the unit should be returned and hasn't been reassigned).

Query 1:

SELECT Q_Master.M_Inventory.SerialNumber, Max(Q_Master.AssignmentDate) AS MaxOfAssignmentDate
FROM Q_Master
GROUP BY Q_Master.M_Inventory.SerialNumber
HAVING (((Max(Q_Master.AssignmentDate)) Is Not Null));

Query 2:

SELECT Q_Master.M_Inventory.SerialNumber, Q_Master.ReturnDate
FROM Query1 INNER JOIN Q_Master ON (Query1.MaxOfAssignmentDate = Q_Master.AssignmentDate) AND (Query1.SerialNumber = Q_Master.M_Inventory.SerialNumber)
WHERE (((Q_Master.ReturnDate) Is Not Null));

[NeoPa]

I think you may be looking for help on Subqueries in SQL.

Let us know if that's what you're after.

[kpfunf]

Perfect, NeoPa. You've been a big help over the years.

[NeoPa]

Very pleased to here it :)