Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
Thanks Rolando - very impressive! –  luckycharms Apr 25 '12 at 14:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.