Rename cell based on duplicate compound key in MS Access

Question

I'm using MS Access (I know, I know...), and I'm trying to rename a cell when there are duplicates of mutliple columns.

Here's an example:

Col_1   Col_2   Col_3   Note
A1      B1      C1      
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2      
A2      B2      C2      Duplicate
A3      B3      C3      
A3      B3      C3      Duplicate
A3      B3      C3      Duplicate

I would like the query or code to update Col_3 (or add another column if that's easier) as below. Note that the order of the duplication is important.

Col_1   Col_2   Col_3   Note
A1      B1      C1      
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2      
A2      B2      C2Dup1  Duplicate
A3      B3      C3      
A3      B3      C3Dup1  Duplicate
A3      B3      C3Dup2  Duplicate

I've seen solutions that use row_number() and over() clauses, but those just work in SQL Server.

Any thoughts for how to tackle this in Access?

Answer 1

You will need two things

First, make sure mytable as a autoincrement column called ID

Next, place this SQL in the SQL View of a Query and run it:

SELECT Col_1,Col_2,IIF(DupKey=0,Col_3,Col3+TRIM$(STR$(ID))) as Column_3 FROM 
(SELECT AA.*,
AA.ID - BB.ID as DupKey
FROM
(
   SELECT A.*,B.ID,
   IIF(DupCOunt=1,B.Col_3,B.Col_3+"Dup") AS Col3
   FROM
   (
      SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
      FROM mytable GROUP BY Col_1, Col_2, Col_3
   ) AS A, mytable AS B
   WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
) as AA,
(
   SELECT A.Col_1,A.Col_2,A.Col_3,A.DupCount,MIN(B.ID) as ID
   FROM
   (
      SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
      FROM mytable GROUP BY Col_1, Col_2, Col_3
   ) AS A, mytable AS B
   WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
   GROUP BY A.Col_1,A.Col_2,A.Col_3,A.DupCount
) as BB
WHERE AA.Col_1=BB.Col_1 AND AA.Col_2=BB.Col_2 AND AA.Col_3=BB.Col_3
) as AAA;

Give it a Try !!!

It will not make consecutive Dup numbers but it will assign unique Dup Numbers. This is too hard to get consecutive numbers without VB Code or another table