Return single result for each unique record ID

[Moss100]

Hello I need to return the first result for each Prop_ID in a query.

The query below returns all records (i.e. a number of records for each ID)

SELECT mqry_tbl_Property.Prop_ID, mqry_tbl_Property.Prop_Address_Sort, mqry_tbl_Filestore.FileStore_Date_Added, mqry_tbl_Filestore.FileStore_Target_Path, mqry_tbl_Filestore.FileStore_TblSup_ID_Link
FROM mqry_tbl_Property LEFT JOIN mqry_tbl_Filestore ON mqry_tbl_Property.Prop_ID = mqry_tbl_Filestore.FileStore_Property_ID_Link
GROUP BY mqry_tbl_Property.Prop_ID, mqry_tbl_Property.Prop_Address_Sort, mqry_tbl_Filestore.FileStore_Date_Added, mqry_tbl_Filestore.FileStore_Target_Path, mqry_tbl_Filestore.FileStore_TblSup_ID_Link
HAVING (((mqry_tbl_Filestore.FileStore_TblSup_ID_Link)=26))
ORDER BY mqry_tbl_Property.Prop_ID, mqry_tbl_Filestore.FileStore_Date_Added DESC;

I have tried a few efforts, but seem to just end up with a single record as opposed to a single record for each ID.

Many thanks Mark

 

[MajP]

QryFirstDatePerProperty

SELECT 
 FileStore_Property_ID_Link,  
 Min(FileStore_Date_Added) as FirstDateAdded, 
FROM 
 mqry_tbl_Filestore 
GROUP BY 
 FileStore_Property_ID_Link

You can then innerjoin to your query by date and ID. Now this can be done by subqueries or selfjoins in one step. See here.

https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

But building two queries is a lot easier if you have not done this before.