Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    7

    Unanswered: check duplicate records within same table

    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.

    Code:
    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not examine your current data, eliminate duplicates, then define a unique index on what ever columns are causing this problem so you users can no longer screw up the data.

    there is a query wizard that can help you identify duplicate rows
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    7
    Healdem,
    Forgive me for not fully explaining my scenario. The records get automatically created and duplicates may happen for several reasons. For example an invoice might fail due to error handling which stops the invoice from being sent out. We correct the problem and resend the invoice. The only difference is the CTRL#. Even though the invoice is not sent it still creates a record. We have a report that I check at the end of the day to see if any invoices that were created and not sent. This is one of 4 reports we have the same concept for advance shipment notification (ASN) as well as documents that have not been acknowledged. For example I send you an invoice and you do not send back a functional acknowledgement that will show up on my report as an invoice being sent BUT not acknowledged. Hope this offers some background information.
    As for the query wizard that can help me identify duplicates I will look into that.

    Thanks,
    -Chris

  4. #4
    Join Date
    May 2012
    Posts
    7
    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

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Never mind. You already saw this.
    Last edited by Sam Landy; 06-06-12 at 17:08.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Don't let crap data into the db
    write a process that takes two approaches
    first off insert any rows which don't already exist
    after that update any rows that already exist

    that means
    no duplicates, no crap data, no need to identify what is a duplicate

    leaving duplicate data is asking, if not begging, for trouble later on.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2012
    Posts
    7
    Healdem,

    I agree with you as far as not letting crap data into the table however that is not an option. I asked my supervisors and they want to see all records even if they are duplicates. I tried explaining that the data is redundant and not needed but like I said they want to see it. I need to come up with a update query that will change the value to "Yes" for the "Duplicate" column for the older duplicate record. Can I do this within the same query? Here is the query that looks for duplicates
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •