check duplicate records within same table

[cthorn112]

Hello All,

I'm working on a query in access 2003 that will check (records within the same table) to see if records are duplicated. Do I need to sort the table? If so I would sort it by FROM_ID, TO_ID, TRAN, PO#, INVOICE#, CTRL#.
Here is an example of duplicate and non duplicate records.

FROM_ID     TO_ID       TRAN  PO#     INVOICE#  CTRL#  Duplicate
1129472024  3133692222  IN    278777  466658    2155   Yes
1129472024  3133692222  IN    278777  466658    2156   No
2128582572  3143640039  IN    280908  466780    3050   No
3567426528  7463004869  IN    153279  108059    2034   No

The logic is based on checking 6 fields. If FROM_ID, TO_ID, TRAN, PO#, INVOICE# are the same and CTRL# from row above is less than CTRL# from row below Duplicate value is Yes, else No. I know how to write it in excel but access is not my forte.
In Excel the formula would go in the Duplicate column:
IF(AND(A3=A4,B3=B4,C3=C4,D3=D4,E3=E4,F3<F4),"Yes","No")

I thought about doing a nested if but it doesn't seem applicable, same with case statement.
Any ideas or suggestions are greatly appreciated.

-Chris

 

[jdraw]

Look at the Duplicate query wizard in Access

 

[cthorn112]

jdraw,
I did not even think of that lol. I will look into that and get back to you. Thanks for the advice.

-Chris

 

[cthorn112]

I used the query wizard to help me identify the duplicate rows.
Now I need to update the "Duplicate" column for the older record to "Yes". If you look at the first two records in my sample data you will see what I'm talking about. I thought the query wizard would give me an option to do so but apparently not. Do I need to write a separate update query or can I do it within the same query? Thanks for reading and any help or suggestions are appreciated.

-Chris

 

[tad0075]

Chris -
Do you have a unique primary key in addition to the data you showed us? If not, there's no way to determine which is older.

I would add an auto-number primary key field, then query out Max or Min of that field when grouped by other fields. Then you can pull out one of your 2 duplicates in each case and mark it as a duplicate with the yes/no indicator you mentioned.

Hope this helps,

- Tyler

 

[cthorn112]

I forgot to paste my code. Any help would be greatly appreciated.

SELECT Edi_out.F3, Edi_out.F4, Edi_out.F5, Edi_out.F6, Edi_out.F7, Edi_out.F8, Edi_out.F9, Edi_out.F10
FROM Edi_out
WHERE (((Edi_out.F3) In (SELECT [F3] FROM [Edi_out] As Tmp GROUP BY [F3],[F6],[F7],[F8],[F9] HAVING Count(*)>1  And [F6] = [Edi_out].[F6] And [F7] = [Edi_out].[F7] And [F8] = [Edi_out].[F8] And [F9] = [Edi_out].[F9])))
ORDER BY Edi_out.F3, Edi_out.F6, Edi_out.F7, Edi_out.F8, Edi_out.F9;

I'm working with existing code so here is a key of what each column name stands for.
F1=Date
F2=Time
F3=FROM_ISA
F4=FROM_NAME
F5=TRAN_TYPE
F6=TO_ISA
F7=TO_NAME
F8=PO_#
F9=INVOICE_#
F10=ENV_CTRL#
F11=GROUP_CTRL#
F12=TRANS_CTRL#
F13=Replaced
F14=Comments

-Chris

 

[cthorn112]

Tyler,

There is a auto increment primary key field in the master table, not this one. This is a temporary table that holds that data until it gets imported into the master table. The way to tell which record is the newest is by the CTRL#. The greater the control number, the newer the record.
What would be the best approach for updating the Duplicate column for the duplicate record? Should I write an update query within the duplicate query? Thanks for the help!

-Chris