CODEWITHSUNDEEP
sqldatabasesortingms-access
Brydan Rogers
Brydan Rogers

Reputation: 23

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.

Upvotes: 2

Views: 3483

Answers (2)

Pat Jones
Pat Jones

Reputation: 898

For Access SQL, this could work:

   SELECT *
   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                                                                                       
   AND                                                                                  
   tA.[Serial Number] = tB.[Serial Number]                                                                                  
   AND                                                                                  
   tA.[Unique ID] = tB.highest_id

Upvotes: 2

Hambone
Hambone

Reputation: 16407

If you create a new query and call it "MaxId":

SELECT [Check In/Out].Component, [Check In/Out].[Serial Number],
    max ([Check In/Out].[Unique ID]) as max_id
FROM [Check In/Out]
group by [Check In/Out].Component, [Check In/Out].[Serial Number]

You can then join to this query from a new query:

SELECT
    [Check In/Out].Component, [Check In/Out].[Serial Number], 
    [Check In/Out].[Unique ID], [Check In/Out].DateTime, 
    [Check In/Out].Intials, [Check In/Out].[Check In/Out]
FROM
    [Check In/Out]
    INNER JOIN MaxId ON 
      [Check In/Out].[Serial Number] = MaxId.[Serial Number] AND
      [Check In/Out].Component = MaxId.Component
WHERE
  [Check In/Out].[Unique ID] = [MaxId].[max_id];

Upvotes: 1

Related Questions