Maximum Date 2

[Genimuse]

Sorry, I'm sure this is a pretty simple query, but I can't seem to get it right, so any help would be appreciated.

Two tables, like this:

PATIENT
-------
ID         -- Autonumber LongInt Primary Key
Name
Etc.

CHECKUP
-------
ID         -- Foreign key to ID in Patient
CheckDate  -- DateTime

One to many relationship between Patient and Checkup. Every time a patient gets a checkup a new record is added to Checkup with the current date.

I'm wanting to select the records in Patient along with the highest (latest) CheckDate for that ID in Checkup

Seems simple enough but somehow I'm messing it up. Any clues for me?

 

[PHV]

Create a saved query, say qryLastDate, with the following sql code:
SELECT ID, Max(CheckDate) As LastDate
FROM Checkup
GROUP BY ID;
Then you can join this query to your Patient table:
SELECT P.ID, P.Name, P.etc, D.LastDate
FROM Patient P LEFT JOIN qryLastDate D ON P.ID = D.ID
;
Depending your version of access you may even do that in a single query:
SELECT P.ID, P.Name, P.etc, D.LastDate
FROM Patient P LEFT JOIN (
SELECT ID, Max(CheckDate) As LastDate FROM Checkup GROUP BY ID
) D ON P.ID = D.ID
;
Note: the left outer join is just in case you have patient without chekup.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[formerTexan]

Hello Genimuse,

The following will work for you.

SELECT Patient.ID, Patient.Name, Max(CheckUp.CheckDate) AS MaxOfCheckDate
FROM Patient INNER JOIN Orders ON Patient.ID = CheckUp.ID
GROUP BY Patient.ID, Patient.Name;

A last unsolicited suggestion: don't use NAME as the name of a field or anything else. Access reserves this and some other words for specific uses and may get a bit confused with your intentions.

Cheers,
Bill

 

[Genimuse]

Thanks to both of you. I ended up going with PH's latter suggestion, which works great. It's the way I was trying to do it but kept messing up the syntax.

And on the names: thanks for the tip, I actually faked up the names of things in the tables for the post (they're really like FirstName, LastName, etc.), so while I do a good job of not using reserved words in the real world, I'm not so cognizant with examples.

Thanks again to you both.

 

[Genimuse]

Ok, now I'm trying to get one additional piece of data out of my "many" table, but the grouping seems to be throwing things off (note that this has nothing to do with my "Double Select" question from earlier today, which solved a different problem).

Here's the current select (simplified):

SELECT P.ID, P.LastName, D.LastDate 
FROM MedicarePatients P 
INNER JOIN (
SELECT ID, MAX(Checkup) AS LastDate FROM MedicareCheckups 
GROUP BY ID
) D ON P.ID = D.ID

I'd like to add in one more piece of data from the MedicareCheckups table, but if I add it in there's a problem (related to the aggregate function, the Max? or the Group?).

Here's what I thought would work:

SELECT P.ID, P.LastName, D.LastDate[COLOR=blue][b], D.Scheduled[/b][/color] 
FROM MedicarePatients P 
INNER JOIN (
SELECT ID, [COLOR=blue][b]Scheduled,[/b][/color] MAX(Checkup) AS LastDate FROM MedicareCheckups 
GROUP BY ID
) D ON P.ID = D.ID

I kinda get why it's not working (though an explanation would be great if you have the time), but don't understand how to change it.

 

[flyover789]

SELECT P.ID, P.LastName, D.LastDate, D.Scheduled
FROM MedicarePatients P
INNER JOIN (
SELECT ID, Scheduled, MAX(Checkup) AS LastDate FROM MedicareCheckups
GROUP BY ID[/blue], Scheduled[blue]
) D ON P.ID = D.ID

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[flyover789]

you could try this:
SELECT P.ID, P.LastName, D.LastDate, D.Scheduled
FROM MedicarePatients P
INNER JOIN (
SELECT ID, Scheduled, MAX(Checkup) AS LastDate FROM MedicareCheckups
GROUP BY ID[blue], Scheduled[/blue]
) D ON P.ID = D.ID

sorry, the first one was too fast (submit i.s.o. preview )

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[pheffley]

I have a similar question in regards to the first entry on this thread. My data is all in one query already and I have repeating part numbers and amounts but need to select the most recent reporting date from the result set of the query:

PARTS_Query
-----
P/N
RPT_DT
AMT

SELECT PJM.PN, PJM.RPT_DT, PJM.AMT
FROM PJM, WIP
WHERE PJM.PN = WIP.PN
AND PJM.AMT>0;

This is probably just as simple, but I can't seem to figure it out.

Thanks,

Paul

 

[lespaul]

assuming that the field ending in DT is the date field, this should work:

SELECT PJM.PN, Max(PJM.RPT_DT), PJM.AMT
FROM PJM
INNER JOIN WIP on PJM.PN = WIP.PN
WHERE PJM.AMT>0
GROUP BY PJM.PN, PJM.AMT;

(next time start a new thread!)

les