CODEWITHSUNDEEP
sqlms-accessselectduplicatesms-access-2010
Alex Silverman
Alex Silverman

Reputation: 203

How do I select *unique* duplicates in MS Access SQL code from one table?

I'm trying to use SQL to select uniquely duplicative service dates, all records of which are contained within a single table. Here's a sample starting table:

GroupID ServiceID ServiceDate
A X 1/1/2010
A Y 1/1/2010
A Z 10/1/2010

I've figured out how to find 2 different records that have the same date and get them in one row, but I end up with a somewhat duplicated table, as it's comparing each record to all others:

GroupID ServiceID1 ServiceDate1 ServiceID2 ServiceDate2
A X 1/1/2010 Y 1/1/2010
A Y 1/1/2010 X 1/1/2010

I don't want the last row, as it's essentially a duplicate of the first.

Any ideas for how to get rid of that second row?

Here's some replication code (and here's my SQL Fiddle: http://sqlfiddle.com/#!9/537d84)

CREATE TABLE MyTable (
  GroupID varchar(255),
  ServiceID varchar(255),
  ServiceDate Date
);

INSERT INTO MyTable (GroupID, ServiceID, ServiceDate)
VALUES ("A", "X", '2010-01-01'),
      ("A", "Y", '2010-01-01'),
      ("A", "Z", '2010-10-01');

SELECT DISTINCT 
  c1.ServiceID AS ServiceID1, c1.ServiceDate AS ServiceDate1, 
  c2.ServiceID AS ServiceID2, c2.ServiceDate AS ServiceDate2
FROM MyTable c1 INNER JOIN MyTable c2 
ON c1.GroupID = c2.GroupID 
WHERE (c1.ServiceID <> c2.ServiceID AND c1.ServiceDate = c2.ServiceDate);

NOTE: I thought I might be able to create A-B and B-A combined ID variables and then SELECT DISTINCT on them. Unfortunately, though, this is a slightly less capable version of SQL than MS Access (techincally, I'm writing it in VBA using the Microsoft.ACE.OLEDB.12.0 connection string), so there is no CONCAT function, nor a CASE WHEN clause, among other deficiencies in syntax efficiency.

Any ideas for how to get rid of that second row?

Upvotes: 1

Views: 1008

Answers (2)

Sola Oshinowo
Sola Oshinowo

Reputation: 609

I have attached three images.

One shows the query design the second shows the query output the third shows the table data(without any query)

enter image description here

enter image description here

enter image description here

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

Since you're using a <> condition, you'll always get duplicate results (x-y and y-x). Instead of using <>, you could arbitrarily decide that the smaller ServiceId should always by in c1:

SELECT DISTINCT 
  c1.ServiceID AS ServiceID1, c1.ServiceDate AS ServiceDate1, 
  c2.ServiceID AS ServiceID2, c2.ServiceDate AS ServiceDate2
FROM MyTable c1 INNER JOIN MyTable c2 
ON c1.GroupID = c2.GroupID 
WHERE (c1.ServiceID < c2.ServiceID AND c1.ServiceDate = c2.ServiceDate); 
-- Here ------------^

Upvotes: 2

Related Questions