Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2012
    Posts
    8

    Unanswered: Unwanted Duplicate Rows

    I have duplicate rows in a query resulting from duplicate rows existing in a table that is used in the query that I cannot change. My query is made up of four different tables. All four tables have the same primay key (PRTNO). Only one of the tables has the data listed multiple times and this is random. Some of the data is repeated as many as four or five times and some of the data is not repeated at all. Please refer to the attached image. I have tried using the unique values property but this doesn't yield the results that I need. I want only one row for each part number (PRTNO). I have very little experience with Access so please bear with me. Any help is appreciated.



    Thank you,

    Roger
    Attached Thumbnails Attached Thumbnails db_duplicates.jpg  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL expression of the query.
    Have a nice day!

  3. #3
    Join Date
    Oct 2012
    Posts
    8
    Below is the SQL

    SELECT dbo_PRTREC.PRTNO, dbo_PRTREC.PRTDESC, dbo_CSTREC.CSTSCMT, dbo_VMCREC.VMCPART
    FROM ((dbo_PRTREC LEFT JOIN dbo_CSTREC ON dbo_PRTREC.PRTNO = dbo_CSTREC.PRTNO) LEFT JOIN dbo_PRTREC AS dbo_PRTREC_1 ON dbo_PRTREC.PRTNO = dbo_PRTREC_1.PRTNO) LEFT JOIN dbo_VMCREC ON dbo_PRTREC.PRTNO = dbo_VMCREC.PRTNO
    WHERE (((dbo_PRTREC.PRTNO) Not Like "*#########*"));

    Thank you,

    Roger

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    A simple way to do it is this:

    1 - remove all this table's relationships from the relationship page temporarily.

    2 - in the database's Tables window, copy and paste the table to a new table; structure only. (This will preserve any indexes, patterns, etc.)

    3 - make a new append query from the old table to the new table, totaled. All fields must be separately enumerated, and all should have the total "Group By." Run the query.

    4 - Open the new table and make sure you have each record once. Close the table.

    5 - Close and delete both this query (you shouldn't need it again) and the old table.

    6 - Rename the temp table with the old table name, and restore the relationships.

    7 - For your own information, and to prevent it from recurring, go through your program and find out why you had redundant records in the first place. Fix the problem. Your other query should be fine after that.

    Sam

  5. #5
    Join Date
    Oct 2012
    Posts
    8
    Hello Sam,
    Thank you for the reply but these tables are tables that I can't change because I am linking to the tables. I have been working with our Oracle DB guru on the duplicate rows. Hopefully he will be able to eliminate them in the linked table.

    Roger
    Last edited by ralexander; 10-30-12 at 09:24.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In that case, for now (until the dupes are removed) just make your query a totals query, and "Group By" the four fields you use.

    Your query should look like
    Code:
    SELECT dbo_PRTREC.PRTNO, dbo_PRTREC.PRTDESC, dbo_CSTREC.CSTSCMT, dbo_VMCREC.VMCPART
    FROM ((dbo_PRTREC LEFT JOIN dbo_CSTREC ON dbo_PRTREC.PRTNO = dbo_CSTREC.PRTNO) LEFT JOIN dbo_PRTREC AS dbo_PRTREC_1 ON dbo_PRTREC.PRTNO = dbo_PRTREC_1.PRTNO) LEFT JOIN dbo_VMCREC ON dbo_PRTREC.PRTNO = dbo_VMCREC.PRTNO
    WHERE (((dbo_PRTREC.PRTNO) Not Like "*#########*")) 
    Group By dbo_PRTREC.PRTNO, dbo_PRTREC.PRTDESC, dbo_CSTREC.CSTSCMT, dbo_VMCREC.VMCPART;
    Sam

  7. #7
    Join Date
    Oct 2012
    Posts
    8
    Hello Sam,
    Thank you for your help. The following worked:

    SELECT dbo_PRTREC.PRTNO, dbo_PRTREC.PRTDESC, [Old FP Database].Package, dbo_CSTREC.CSTSCMT, First(dbo_VMCREC.VMCPART) AS FirstOfVMCPART
    FROM ((dbo_PRTREC LEFT JOIN dbo_CSTREC ON dbo_PRTREC.PRTNO = dbo_CSTREC.PRTNO) LEFT JOIN [Old FP Database] ON dbo_PRTREC.PRTNO = [Old FP Database].PRTNO) LEFT JOIN dbo_VMCREC ON dbo_PRTREC.PRTNO = dbo_VMCREC.PRTNO
    GROUP BY dbo_PRTREC.PRTNO, dbo_PRTREC.PRTDESC, [Old FP Database].Package, dbo_CSTREC.CSTSCMT
    HAVING (((dbo_PRTREC.PRTNO) Not Like "*#########*"))
    ORDER BY dbo_PRTREC.PRTNO;

    Thanks again,

    Roger

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Glad to have been of help.

    Sam

Posting Permissions

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