Solved: Latest Record

[Imdabaum]

I have a query that pulls information from five tables.

table1 holds specific information about properties.
table2.columnA linked to table1.columnX
table3.columnA linked to table1.columnY
table4.columnA links to table1.columnZ
table5.columnA links to table1.columnA.


SELECT table1.Title, table1.Nbr, table2.short_name, table4s.Description AS Type, table3.description, table1.WO, table1.DateReceived, table5.MEMO, table5.REP
FROM table4s LEFT JOIN (table5 RIGHT JOIN ((table1 LEFT JOIN table2 ON table1.DT = table2.DT_ID) LEFT JOIN table3 ON table1.Phase = table3.phase_id) ON table5.pro_id = table1.ID) ON table4.Type_ID = table1.ProjectType;


table5 has pro_id to link it to the project the note refers to.
status_date = date that the MEMO was created.
MEMO= the actual note.

Is there a way to run this query and just get the most recent MEMO for each project instead of returning all MEMOS?

[orange]

I have a query that pulls information from five tables.

table1 holds specific information about properties.
table2.columnA linked to table1.columnX
table3.columnA linked to table1.columnY
table4.columnA links to table1.columnZ
table5.columnA links to table1.columnA.


SELECT table1.Title, table1.Nbr, table2.short_name, table4s.Description AS Type, table3.description, table1.WO, table1.DateReceived, table5.MEMO, table5.REP
FROM table4s LEFT JOIN (table5 RIGHT JOIN ((table1 LEFT JOIN table2 ON table1.DT = table2.DT_ID) LEFT JOIN table3 ON table1.Phase = table3.phase_id) ON table5.pro_id = table1.ID) ON table4.Type_ID = table1.ProjectType;

table5 has pro_id to link it to the project the note refers to.
status_date = date that the MEMO was created.
MEMO= the actual note.

Is there a way to run this query and just get the most recent MEMO for each project instead of returning all MEMOS?
You could try this SQL

SELECT table1.Title
, table1.Nbr, table2.short_name
, table4s.Description As Type, table3.description
, table1.WO, table1.DateReceived
, (SELECT table5.MEMO from table5
WHERE status_date =(SELECT MAX(status_date) from table5,table1
WHERE table5.pro_id = table1.id))
, table5.REP
FROM
table4s
LEFT JOIN (table5 RIGHT JOIN
((table1 LEFT JOIN table2 ON table1.DT = table2.DT_ID)
LEFT JOIN table3 ON table1.Phase = table3.phase_id)
ON table5.pro_id = table1.ID)
ON table4.Type_ID = table1.ProjectType;
If this doesn't get you what you want, let us know and we'll try again.
Good luck.

[Imdabaum]

That worked great... sorry I rushed off so fast to finish my project I failed to close the thread. Thanks for your help.