Max date off of 1 table Multiple fields 1

[John1Chr]

I would like to grab the Nickname off of Table 1 where the EFFDT is the greatest - (ie. Sue was the nickname on 7/1/2012.) I want to show the fields Person nickname and effdt. I'm thinking this is fairly simple but can't seem to figure out.


Person NickName Status EFFDT
Susan Susie A 01-Jan-01
Susan Sue A 01-Jul-12

 

[Andrzejek]

One way to do it:

Select * From MyTable
Where EFFDT = ([blue] Select Max(EFFDT) From MyTable[/blue])

You may need some #'s around the date....

Have fun.

---- Andy

 

[John1Chr]

It looks like it is working for the ones that have a double. The ones that only have one date are being ignored.

 

[Andrzejek]

Is you problem solved? Or do you need any additional help?

If you do need more help, please provide a better example of your data and the outcome you hope to get.

Have fun.

---- Andy

 

[John1Chr]

I apologize, if there is a Kevin with just one efft date that gets ignored.

 

[randy700]

Select * From MyTable
Where EFFDT = ( Select Max(EFFDT) From MyTable)
GROUP BY Name

Randy

 

[dhookom]

Try add the Person field into the subquery:

Select * 
From MyTable
Where EFFDT = (Select Max(EFFDT) From MyTable M WHERE M.Person = MyTable.Person)

Duane
Hook'D on Access
MS Access MVP

 

[John1Chr]

Thanks,

You both helped me solve. In the end I used the Last to get the latest Nickname. Here was the final SQL that works:

SELECT PERSON_TBL.PERSON, Max(PERSON_TBL.EFFDT) AS MaxOfEFFDT, Last(PERSON_TBL.NICKNAME) AS LastOfNICKNAME
FROM PERSON_TBL
GROUP BY PERSON_TBL.PERSON;

 

[dhookom]

I would never rely on LAST to return the correct value.

Duane
Hook'D on Access
MS Access MVP

 

[John1Chr]

It appears to work...it is grabbing the one with the latest EFFDT. Is there a safer or better method?

 

[dhookom]

You didn't respond to my suggestion. Did it work? If not, what results were wrong?

Do you have some actual table and field names and more sample records?

Duane
Hook'D on Access
MS Access MVP

 

[John1Chr]

Duane,

You were correct. Where the field has the same name it doubled up with the Last Command when I tried to link to.

 

[PHV]

I'd try this:

SELECT A.PERSON, A.EFFDT, A.NICKNAME
FROM PERSON_TBL A INNER JOIN (
SELECT PERSON, Max(EFFDT) AS MaxOfEFFDT FROM PERSON_TBL GROUP BY PERSON
) L ON A.PERSON=L.PERSON AND A.EFFDT=L.MaxOfEFFDT

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

Thanks PHV....Dhookum's worked.