only records with the last dates

  • Thread starter Thread starter Michel Peeters
  • Start date Start date
M

Michel Peeters

SELECT tblLubricationSchedule.DateDone, tblMachinery.Description

FROM (tblMachinery INNER JOIN tblLubricationPart ON tblMachinery.MachineryId
= tblLubricationPart.MachineryId) INNER JOIN tblLubricationSchedule ON
tblLubricationPart.TblLubricationPartId =
tblLubricationSchedule.TblLubricationPartId;





How can I change above SQL so that for each "MachineryId", only the latest
"DateDone" record is shown?

I tried with Dmax(".... but I cannot make it to work.



Tks,

Michel P.
 
Use a subquery, like this:

SELECT tblMachinery.Description,
(SELECT Max(tblLubricationSchedule.DateDone)
FROM tblLubricationSchedule
WHERE tblLubricationSchedule.MachineryId = tblMachinery.MachineryId)
As LastLube
FROM tblMachinery;

You should be able to sort out the tblLubricationPart side of things from
there.
 
Dear Michel:

First, adding some aliases, which will come in handy later:

SELECT S.DateDone, M.Description
FROM (tblMachinery M
INNER JOIN tblLubricationPart P
ON M.MachineryId = P.MachineryId)
INNER JOIN tblLubricationSchedule S
ON P.TblLubricationPartId = S.TblLubricationPartId;

Next, adding the criterion:

SELECT S.DateDone, M.Description
FROM (tblMachinery M
INNER JOIN tblLubricationPart P
ON M.MachineryId = P.MachineryId)
INNER JOIN tblLubricationSchedule S
ON P.TblLubricationPartId = S.TblLubricationPartId;
WHERE S.DateDone = (SELECT MAX(DateDone)
FROM tblLubricationSchedule S1
WHERE S1.TblLubricationPartId = S.TblLubricationPartId)

Note that, if there are two rows in tblLubricationSchedule with the
same TblLubricationPartId with the same DateDone, and if this is the
latest DateDone, then you'll get both, since there's no way to
distinguish which is the "latest" one.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top