I used the query wizard to help me identify the duplicate rows.
Now I need to update the "Duplicate" column for the older record(s) 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.
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
Here is the code
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
Bookmarks