Query latest record for each master record

[Guest]

Hi

I need a query to retrieve only the latest "note" for each "project" in my
database. The "note" table is linked to "project" by a field called
ProjectID. Each week I update the notes for each project that I am managing
but often, there is no change so we might as well use the previous note. So
how do I filter the notes to show the latest record for each project?

Iain

 

[Guest]

Which field do you use to indicate the order of insert of records to your
table?
A counter, or a date field
If you don't have such a field that indicate the order the records been
inserted to the table, you will need to add one to get the query that you
want.

there is an example how to retrieve the records by the last date inserted,
if you use a counter you can change the datefield to the counter name.


SELECT M1.[project], M1.Note
FROM TableName AS M1
WHERE M1.[dateField] In (SELECT Top 1 M2.[dateField]
FROM TableName as M2
WHERE M2.project=M1.project And
M2.Note=M1.Note
ORDER BY M2.[date] Desc)