Return one record per project by max date

[wetsnow13]

Hi,

I have three tables:
tblProj, tblProjNotes, tblProjNotesType

tblProjNotes has 2 foreign keys (and a PK) - to tblProj and to tblProjNotesType

Each project has multiple notes and each note has a type designation and a date.

I would like to query the newest dated note for each project and return the note type.



For responders: Classify me as newbie - I rely on the QBE, but am getting better at turning it over to the SQL

Thanks in advance!
W

[pbaldy]

Is this appropriate?

http://www.baldyweb.com/LastValue.htm

[wetsnow13]

Hi pbaldy,

I actually just found that link in another forum - after searching for answers for 2 days - used it and it worked beautifully!

Perhaps I need to refine my Googling as well

this is what I did - basically an exact copy of your link:

SELECT qrySubAll.*
FROM qrySubAllMax INNER JOIN qrySubAll ON (qrySubAllMax.ProjProjectID = qrySubAll.ProjProjectID) AND (qrySubAllMax.MaxNoteDate = qrySubAll.ProjNotesDate);

the qrySubAll whittled down the notes table to submittal types of notes and the qrySubAllMax took that and got the max date for each project. Thank you for showing me how to link these queries to return all the columns I need!

[pbaldy]

Happy to help!