Top Values Based on Field 2

[wonderwhy]

I have read and read but can't figure out how to do this...
I need to create a query that only pulls the records with the most recent date based on a field in the table (in the example below this field would be SSN).

So, for example, if this is my data:
ReminderNum SSN DateSent
1 333-99-8888 10/10/2003
2 333-99-8888 11/11/2003
3 444-99-8888 10/10/2003
4 444-99-8888 11/11/2003

Then I want the results to be:
ReminderNum SSN DateSent
2 333-99-8888 11/11/2003
4 444-99-8888 11/11/2003

Help!
Thanks,
Julia

 

[nicsin]

Hi Julia,

try:

select *
from myTable
having dateSent = max(dateSent)

nicsin

 

[wonderwhy]

Nicsin,

I don't use SQL so this doesn't make much sense to me. Is there a way to do this in the query grid? Or, can you be more specific?

Thank you

Julia

 

[r937]

Queries > New > Design view > close the Show Table box

View > SQL view

paste the following:

[tt]select ReminderNum, SSN, DateSent
from yourtable XX
where DateSent =
( select max(DateSent)
from yourtable
where SSN = XX.SSN )[/tt]

Query > Run


rudy

http://r937.com/

 

[wonderwhy]

Rudy,

This only gave me the record that had the max value in the DateSent field (actually named 'ReportSent'). Here's what the SQL looked like after I used my actual field names:

SELECT ReportID, ProtocolNumber, ReportTypeID, ReportSent,ReportDue, ReportReceived, ReportNotes
FROM tblProgressReports
WHERE ReportSent=
(Select max(ReportSent)
from tblProgressReports
Where ProtocolNumber=tblProgressReports.ProtocolNumber)

I want the query to show one record for each 'ProtocolNumber' based on the record with the most recent date in the 'ReportSent' field. ProtocolNumber is a foreign key so there are many records for each ProtocolNumber in this table.

Thank you!

Julia

 

[lespaul]

Try this (paste into the SQL View):

SELECT ReportID, ProtocolNumber, ReportTypeID, Max(ReportSent), ReportDue, ReportReceived, ReportNotes
FROM tblProgressReports
GROUP BY ReportID, ProtocolNumber, ReportTypeID, ReportDue, ReportReceived, ReportNotes

 

[JonFer]

I always alias the tables in the subquery like so:

SELECT ReportID, ProtocolNumber, ReportTypeID, ReportSent,ReportDue, ReportReceived, ReportNotes
FROM tblProgressReports
WHERE ReportSent=
(Select max(a.ReportSent)
from tblProgressReports as a
Where a.ProtocolNumber=tblProgressReports.ProtocolNumber)

 

[Golom]

You need to alias the tables so that you can refer to different instances of the table in the sub-select as Rudy did.

SELECT ReportID, ProtocolNumber, ReportTypeID, ReportSent,ReportDue, ReportReceived, ReportNotes
FROM tblProgressReports As XX
WHERE ReportSent=
(Select max(ReportSent)
from tblProgressReports
Where ProtocolNumber=XX.ProtocolNumber)

 

[wonderwhy]

JonFer and Golom, your solutions each worked perfectly, although I don't understand a bit of either! Golom's SQL looks simpler but I really don't get the aliasing thing.

Well, I'll keep looking at it... In the meantime thanks to everyone who helped!

Julia

 

[Golom]

wonderwhy

The "aliasing thing" is really fairly simple. You are using two instances of the same table (tblProgressReports), one in the outer query and one in the sub-query in the WHERE clause. You give them (or at least one of them) an alias so that you can refer to them by different names and thus distinguish which one you're talking about. The only difference between JonFer's solution and mine is that JonFer decided to attach an alias to the version of tblProgressReports in the sub-query while attached it to the one in the main query.