Need to use a min date fn 1

[robinsql]

There are several records for each TyreHist.[Serial Number] in the result set from the following query. I only want the record pertaining to the earliest TyreHist.Date for each serial number. Is this possible? Any help gratefully received. Thanks.


SELECT TyreHist.Date, TyreHist.[Serial Number], Tyrehist.Cost, TyreHist.Action, TyreHist.Client_ID, TyreHist.Depot_ID, Depots.[Depot Name], Clients.[Client Name], Vehicles.[Vehicle Name], Compounds.Desc AS Compound, TyreManuf.Desc AS [Manuf Desc], TyrePatterns.Desc AS [Pattern Desc], TyreSize.Desc AS Size, TyreHist.T_Pos_From AS [T_Pos_From], Tyres.Cost AS Life FROM ((((((((Compounds INNER JOIN TyreConfig ON Compounds.ID = TyreConfig.CompoundID) INNER JOIN Tyres ON TyreConfig.[Config ID] = Tyres.[Config ID]) INNER JOIN TyreHist ON (Tyres.[Serial Number] = TyreHist.[Serial Number]) AND (TyreConfig.[Config ID] = TyreHist.[TConfig ID])) LEFT JOIN Clients ON TyreHist.Client_ID = Clients.ID) LEFT JOIN Depots ON TyreHist.Depot_ID = Depots.ID) LEFT JOIN TyreManuf ON TyreHist.Manuf_ID = TyreManuf.ID) LEFT JOIN TyrePatterns ON TyreHist.Pattern_ID = TyrePatterns.ID) LEFT JOIN TyreSize ON TyreHist.Size_ID = TyreSize.ID) INNER JOIN Vehicles ON TyreHist.Location = Vehicles.Veh_ID WHERE [Action]=5 AND TyreHist.Date>=DateValue('30/09/2000') AND TyreHist.Date<= DateValue('03/10/2002')

 

[tsumrall]

Make a &quot;group by&quot; query with two fields.

1. Serial Number as Group by
2. Date as Min

This should give you 1 record per serial number and it's earist date.

Link this to your query above by Serial Number and date and you should only get those records.