CODEWITHSUNDEEP
sql-serverms-accessduplicates
RJIGO
RJIGO

Reputation: 1943

Duplicate Query in Microsoft Access or SQL Server

I have over 1,000 entries with duplicate values in an Access database. I want to create a column called Duplicate and set it to true if a record is NOT THE FIRST ONE with a particular value. This means, if the record is the first one with the value "Red Chair", the Duplicate field is set to false, but all subsequent records with value "Red Chair" will have the Duplicate field set to true.

How do I perform this query in Access?

This database will be upsized to an SQL Server database, so an option for me is to 'ignore' the duplicate records while retrieving the records in my SQL query. If this option is viable, I'd like to know how to do this in SQL as alternative. Thanks.

Upvotes: 0

Views: 358

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

I'm no expert on the Access dialect, but this adaptation of RJIGO's answer or something similar may also work and be more efficient:

UPDATE Tabelle1 SET
  b = 'Duplicate'
WHERE 
  Tabelle1.[ID] > (
    SELECT min([id])
    FROM [Tabelle1] as T2
    WHERE T2.[a] = Tabelle1.[a]
  );

Upvotes: 1

phsaires
phsaires

Reputation: 2378

I hope this sql help u:

SELECT table.field, Count(table.field) AS test, IIf([test]>1,"TRUE","FALSE") AS check FROM table GROUP BY table.field, IIf([test]>1,"TRUE","FALSE");

Upvotes: 1

user783388
user783388

Reputation:

You will have to use subqueries. Try this

UPDATE Tabelle1 SET Tabelle1.b = 'Duplicate'
WHERE 
((Tabelle1.[ID] In 
 (SELECT Tabelle1.[ID] FROM Tabelle1 WHERE 
  ((Tabelle1.[a] In 
     (SELECT [a] FROM [Tabelle1] As Tmp GROUP BY [a] HAVING Count(*)>1 )
   )
  AND 
  (Tabelle1.[ID] Not In 
    (SELECT min([id]) FROM [Tabelle1] as grpid GROUP BY [a] HAVING Count(*)>1)
  ));
)));

Upvotes: 2

Related Questions