2

So, I have a table with 3 columns, of which the first column consists of IDs and the last column consists of dates. What I need is, to sort the table by dates, and remove any duplicate IDs with a later date (and keep the ID with the earliest date). For example, This is how my table originally looks like -

  • 123 Ryan 01/01/2011
  • 345 Carl 03/01/2011
  • 123 Lisa 01/02/2012
  • 870 Tiya 06/03/2012
  • 345 Carl 07/01/2012

I want my resultant table to look like this -

  • 123 Ryan 01/01/2011
  • 345 Carl 03/01/2011
  • 870 Tiya 06/03/2012

I'm using VBA Access Code to find a solution for the above, and used SQL Queries too, however my resultant table either has no duplicates whatsoever or displays all the records. Any help will be appreciated.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Nams
  • 47
  • 1
  • 9
  • Is adding a unique ID to your table an option for you? – Fionnuala Apr 18 '12 at 19:05
  • Unfortunately, not. Although I could create a new table which will display the results of this table without the unique ID (if that helps). I just want to display the first row of each of the duplicate records including the remaining unduplicated records. – Nams Apr 18 '12 at 19:08

1 Answers1

2

This will create a new table:

SELECT tbl.SName, a.ID, a.BDate
INTO NoDups
FROM tbl 
INNER JOIN (
    SELECT  ID, Min(ADate) As BDate 
    FROM tbl GROUP BY ID)  AS a 
ON (tbl.ADate = a.BDate) AND (tbl.ID = a.ID);
Fionnuala
  • 90,370
  • 7
  • 114
  • 152