Need Current state for each record

[littleccguy]

Just learning. Please be gentle.

I have two tables - ReportRequest (Main Table) and ReportHistory
(progress tracking). ReportRequest shows each record once (RequestID
is primary key). The ReportHistory tracks everytime the request has
changed status (HistoryID is primary key and RequestID is in table for
join purposes).

I need to edit my query so that every RequestID shows once with the
most current (highest) HistoryID.

Told you I was just learning.

Win XP and Access 2003

Here is the starting Query

SELECT ReportRequest.RequestId, ReportHistory.HistoryID,
ReportHistory.RequestID, ReportHistory.ReportStatus,
ReportHistory.ReportStatusChangeDate, ReportHistory.Notes
FROM ReportRequest INNER JOIN ReportHistory ON ReportRequest.RequestId
= ReportHistory.RequestID;

Thanks

 

[Guest]

Try this. I only tested it with a small record set.
SELECT ReportRequest.RequestId, Max(ReportHistory.ReportStatusChangeDate) AS
MaxOfReportStatusChangeDate, Last(ReportHistory.ReportStatus) AS
LastOfReportStatus, ReportHistory.Notes
FROM ReportRequest INNER JOIN ReportHistory ON ReportRequest.RequestId =
ReportHistory.RequestId
GROUP BY ReportRequest.RequestId, ReportHistory.Notes;

 

[littleccguy]

Unfortunately that didn't work. It returned all rows.

Although, I kind of see where you are going with that.

 

[Roger Carlson]

I'd try a subquery, something like this:

SELECT ReportRequest.RequestId, ReportHistory.HistoryID,
ReportHistory.RequestID, ReportHistory.ReportStatus,
ReportHistory.ReportStatusChangeDate, ReportHistory.Notes
FROM ReportRequest INNER JOIN ReportHistory ON ReportRequest.RequestId =
ReportHistory.RequestID
WHERE ReportHistory.HistoryID In (SELECT Max(HistoryID) as MaxID FROM
ReportHistory GROUP BY RequestID);

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[littleccguy]

YYYEEEEEEAAAAAAAAAHH!
yeah
yeah
yeaaaaaahh

Thanks so much!

 

[Roger Carlson]

It worked, I take it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L