Brydan Rogers Brydan Rogers - 1 year ago 82
SQL Question

Access SQL Query: How to only display most recent record of duplicates

I'm very new to SQL and am trying to create a query that will sort a table (I've got that part working) and then only display the most recent record for each respective component and serial number.

Basically I'm making a check in/out system and want to only list the most recent status of for each particular component. Each time a component is checked in and out, it is issued a unique ID for that particular check in/out. Therefore I know that I can use the highest Unique ID for determining which record to display in my output table.

enter image description here

(Top graph is what I have, bottom is what I want)

How do I go about removing records in which the first two fields are duplicated?

Heres what I have so far:

SELECT [Check In/Out].Component, [Check In/Out].[Serial Number], [Check In/Out].[Unique ID], [Check In/Out].DateTime, [Check In/Out].Initials, [Check In/Out].[Check In/Out]
FROM [Check In/Out]
ORDER BY [Check In/Out].Component, [Check In/Out].[Serial Number], [Check In/Out].[Unique ID];

I found a similar code (shown below), however my attempt at modifying the code to work with mine failed:

SELECT Customers.ID, Customers.FullName, Customers.SaleDate
FROM Customers
WHERE [SaleDate] =
(SELECT Max([SaleDate])
FROM Customers AS X
WHERE X.[FullName] = [Customers].[FullName])

Any help would be greatly appreciated.

Answer Source

For Jet SQL (which is the flavor of SQL that Access uses), this could work:

   FROM [Check In/Out] AS tA INNER JOIN (SELECT Component, [Serial Number],    
                                            MAX(Unique ID) AS highest_id
                                         FROM [Check In/Out]
                                         GROUP BY Component, [Serial Number]) AS  

   tB ON tA.Component = tB.Component                                                                                       
   tA.[Serial Number] = tB.[Serial Number]                                                                                  
   tA.[Unique ID] = tB.highest_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download