remove Left Join duplicates

[lokiluke]

Hi all, First off i am new to SQL and access. i have a database that has two related tables, tblAssets and tblTests. they are linked one to many by the Primary and foreign key tblAssets.AssetID and tblTests.AssetID.



i have written a simple LEFT JOIN to match up tblTests.TestDate with its appropriate AssetID

SELECT tblassets.assetid, tbltests.testdate
FROM tblAssets LEFT JOIN tblTests ON tbltests.AssetID=tblAssets.AssetID

as there are multiple entries for each asset in the tblTests i would like show only the latest date for each AssetID. i have tried googling but to no avail. i have tried using MAX(tblTests.Testdate) but this only locates the max date in the table, not for each group of assetID.

any hints on how to acheive this

Cheers,
luke

[alansidman]

Create an aggregate query and group on the assets with a Max on the date. Here is a tutorial that explains.

http://www.techonthenet.com/access/queries/index.php

Scroll down to the group by features and click on Max.

Alan

[lokiluke]

cheers, exactly what i needed. have been playing round with the max() but the tutorial showed me where i was going wrong.

thanks,
alan