Hi Guys,
The Query below finds the most recent record in a table according to a date field, my problem is i don't know how to integrate this Query into the Query below this Query to replace the "Last" function
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The "Last" function finds the last record in that table, but i need to find the most recent classificationid in the table according to reclassificationDate field. (same for squadID)
The Query below finds the most recent record in a table according to a date field, my problem is i don't know how to integrate this Query into the Query below this Query to replace the "Last" function
Code:
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,
tblClassificationHistory.reclassificationDate
FROM tblinmate
INNER JOIN tblClassificationHistory
ON tblinmate.inmateID = tblClassificationHistory.inmateID
) a
INNER JOIN (
SELECT tblClassificationHistory.inmateID,
MAX(tblClassificationHistory.reclassificationDate) as max_date
FROM tblinmate
INNER JOIN tblClassificationHistory
ON tblinmate.inmateID = tblClassificationHistory.inmateID
GROUP BY tblClassificationHistory.inmateID
) b
ON a.inmateID = b.inmateID
AND a.reclassificationDate = b.max_date
ORDER BY a.inmateID;The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The "Last" function finds the last record in that table, but i need to find the most recent classificationid in the table according to reclassificationDate field. (same for squadID)
Code:
SELECT
tblinmate.statusid,
tblinmate.activedate,
Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
Last(tblsquadhistory.squadid) AS LastOfsquadID,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,
tblinmate.prisonnumber,
tblinmate.droppeddate,
tblinmate.personid,
tblinmate.inmateid
FROM tblsquad
INNER JOIN (tblperson
INNER JOIN ((tblinmate
INNER JOIN (tblclassification
INNER JOIN tblclassificationhistory
ON tblclassification.classificationid =
tblclassificationhistory.classificationid)
ON tblinmate.inmateid =
tblclassificationhistory.inmateid)
INNER JOIN tblsquadhistory
ON tblinmate.inmateid =
tblsquadhistory.inmateid)
ON tblperson.personid = tblinmate.personid)
ON tblsquad.squadid = tblsquadhistory.squadid
GROUP BY tblinmate.statusid,
tblinmate.activedate,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,
tblinmate.prisonnumber,
tblinmate.droppeddate,
tblinmate.personid,
tblinmate.inmateid;
Last edited:
I had all my tables with lookups before and removed them on I think it was yours or Gemmathehusks advice. But i havent started working on those tables/forms yet i left them as they where. But i promise i will get to those as well. I needed to sort out the main Query first and its done thanks to your nifty little Combination of Queries you showed me.
