Adding a Field But Not to the Aggregate Function 1

[Genimuse]

(This may be a repeat of sorts, but I think it got buried at the end of my previous question.)

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.

 

[PHV]

Something like this ?
SELECT P.ID, P.LastName, D.LastDate, S.Scheduled
FROM MedicarePatients P
INNER JOIN (
SELECT ID, MAX(Checkup) AS LastDate FROM MedicareCheckups
GROUP BY ID
) D ON P.ID = D.ID
INNER JOIN MedicareCheckups S ON D.ID=S.ID AND D.LastDate=S.Checkup

May need some tweaking for parentheses in the joins ...

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

 

[Genimuse]

That makes sense, joining it to that table again, but I can't figure out why parentheses would be needed (or where), yet as you guessed there's a syntax problem with the query. I'm feeling like a bear of very few brains, but the few guesses I've made don't work.

 

[PHV]

I have not Access at hand for the moment, so just a guess:
SELECT P.ID, P.LastName, D.LastDate, S.Scheduled
FROM (MedicarePatients P
INNER JOIN (
SELECT ID, MAX(Checkup) AS LastDate FROM MedicareCheckups
GROUP BY ID
) D ON P.ID = D.ID)
INNER JOIN MedicareCheckups S ON D.ID=S.ID AND D.LastDate=S.Checkup

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

 

[Genimuse]

That makes perfect sense, thank you. I really feel thick the last few days, belying my Tipmaster title. :-D

 

[Genimuse]

Ok, I've taken that and added in all my WHEREs and ORDER BYs and such, but suddenly I'm getting an unexpected result. Here's the full query (sorry, I have to provide the whole thing this time because that's where the problem is):

SELECT P.ID, P.LastName, P.FirstName, P.Chart, P.BirthdayMonth, P.BirthdayDay, P.Phone, P.MCStatus, P.Hold, D.LastDate, S.Scheduled, P.Notes 
FROM (
    MedicarePatients P INNER JOIN (
        SELECT ID, Max(Checkup) As LastDate FROM MedicareCheckups GROUP BY ID
    ) D 
    ON P.ID = D.ID
) INNER JOIN MedicareCheckups S ON D.ID=S.ID AND D.LastDate=S.Checkup 
WHERE D.LastDate > #10/01/2004# AND D.LastDate <= #10/31/2004# AND P.Hold = False 
ORDER BY D.LastDate ASC, P.LastName ASC

The query is returning the Scheduled field now, as I needed, but suddenly I'm only getting rows where there's something in the Notes field (in the MedicarePatients table) -- if there's nothing in that field then the row isn't returned. Yet my WHEREs have nothing to do with that field. Do I need some kind of left join or outer join or something?