Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2009
    Posts
    9

    Unanswered: Query Duplicating Data?

    I'm trying to run a query using 2 linked tables in Acess 2007. I've been using the same query for a while, with no problem. However, recently, it seems like some of the items in my table are being duplicated when I run the query. When I chack the tabel itself, there is no duplication. However, when running a query, I find duplicates, and when summing, the totals are not correct. How can I stop this from happening?

    I am not very experienced with Access, and use the Query Design button - I don't know how to write the code myself.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Welcome to the site. What is the SQL of the query? Does it refer to more than one table? Often duplication of records can be traced to an incorrect join between the tables.
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Often duplication of records can be traced to an incorrect join between the tables.
    I'd almost go so far as to say that duplication of records can always be traced to an incorrect join.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    always? no

    if the query has been working correctly, and then suddenly starts producing duplicates, it is often due to duplicate data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I did say almost
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2009
    Posts
    9
    Hmm. I just used the join properties message box. I believe it's set up so to include all the records from 1 table, and only those that match from the other table. Like I said, I really don't know much about Access, and nothing about SQL.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you can post the database it may help us see the problem.
    Paul

  8. #8
    Join Date
    Jan 2009
    Posts
    9
    I would, as I really need the help, but the database is rather large, and contains confidential information.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by lbequis
    I would, as I really need the help, but the database is rather large, and contains confidential information.
    ok so to diagnose if its a duff data problem run the matched query wixzard or find duplicates wizard, what ever its called.. that should help identify any duplicates or other problems. of course if you know what the duplicates are you could dive straight into the table and examine any rows with the know duplicate values

    if it is duplicate data then you need to work out how duplicate data has been captured, and consider if the design needs revisiting, or how you stop this occurring again. normally I'd say that is down to design flaws

    if you can't find duplicate data then it may be a duff join. in which case you need to revisit the queries and join statements to make sure the SQL is doing what you think it should be (as opposed to what it is doing)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you show us the SQL of the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2009
    Posts
    9
    I apologize for the lack of any sort of formatting here. I don't write the SQL, I just copied and pasted.

    SELECT Year([QO-NTF]![Company # 01 - Combined - First Order Date]) & IIf(Month([QO-NTF]![Company # 01 - Combined - First Order Date])<10,"0","") & Month([QO-NTF]![Company # 01 - Combined - First Order Date]) AS [Date], [QO-NTF].[Company # 01 - Combined - First Order Channel], Keycodes.CATEGORY, Keycodes.DROP, Count([QO-NTF].[Database Key]) AS [CountOfDatabase Key], Sum([2007 Matchback].Dollars) AS SumOfDollars
    FROM ([QO-NTF] INNER JOIN [2007 Matchback] ON [QO-NTF].[DOV-SOURCE-ORDER] = [2007 Matchback].[Source-Order]) INNER JOIN Keycodes ON [2007 Matchback].[Original Order File KeyCode] = Keycodes.KEYCODE
    WHERE ((([2007 Matchback].Match)="NM"))
    GROUP BY Year([QO-NTF]![Company # 01 - Combined - First Order Date]) & IIf(Month([QO-NTF]![Company # 01 - Combined - First Order Date])<10,"0","") & Month([QO-NTF]![Company # 01 - Combined - First Order Date]), [QO-NTF].[Company # 01 - Combined - First Order Channel], Keycodes.CATEGORY, Keycodes.DROP;

  12. #12
    Join Date
    Jan 2009
    Posts
    9
    Sorry, I'm not sure if I was actually having a problem with the one above. I know that this one is definitely a problem though. I know that my table itself does not have duplicate data. I thoroughly looked into that initially, believing that I had figured out what was going wrong.


    SELECT [DOV-2009 Matchback].[Order Date], [DOV-KEYCODES].CATEGORY, [DOV-KEYCODES].DROP, [DOV-2009 Matchback].Channel, Sum([DOV-2009 Matchback].Dollars) AS SumOfDollars, Count([DOV-2009 Matchback].Dollars) AS CountOfDollars
    FROM [DOV-2009 Matchback] INNER JOIN [DOV-KEYCODES] ON [DOV-2009 Matchback].[Original Order File KeyCode] = [DOV-KEYCODES].KEYCODE
    WHERE ((([DOV-2009 Matchback].Match)="NM"))
    GROUP BY [DOV-2009 Matchback].[Order Date], [DOV-KEYCODES].CATEGORY, [DOV-KEYCODES].DROP, [DOV-2009 Matchback].Channel
    HAVING ((([DOV-2009 Matchback].[Order Date]) Between #1/12/2009# And #1/18/2009#));

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what kind of problem does the query in post #12 have?

    it couldn't have duplicates, because there's a GROUP BY clause, and a GROUP BY clause cannot produce duplicates by definition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2009
    Posts
    9
    The problem is that when summing things that should add up to the total number of records, the total is too high. For example, is the table had 500 records the output indicates that there are 510. If I go and get rid of the grouping, I find duplicates in the query output. If I go to the original table, there are still no duplicates.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    classic one-to-many relationship

    if table A has 500 rows, and each row in table A can have one or more rows in table B, then when you join table A to table B, it is quite possible that you will get more than 500 rows

    i suspect the problem lies here, marked in red --
    Code:
    SELECT [DOV-2009 Matchback].[Order Date]
         , [DOV-KEYCODES].CATEGORY
         , [DOV-KEYCODES].DROP
         , [DOV-2009 Matchback].Channel
         , Sum([DOV-2009 Matchback].Dollars) AS SumOfDollars
         , Count([DOV-2009 Matchback].Dollars) AS CountOfDollars
      FROM [DOV-2009 Matchback] 
    INNER 
      JOIN [DOV-KEYCODES] 
        ON [DOV-2009 Matchback].[Original Order File KeyCode] = [DOV-KEYCODES].KEYCODE
     WHERE (
           (
           ([DOV-2009 Matchback].Match)="NM"
           )
           )
    GROUP 
        BY [DOV-2009 Matchback].[Order Date]
         , [DOV-KEYCODES].CATEGORY
         , [DOV-KEYCODES].DROP
         , [DOV-2009 Matchback].Channel
    HAVING (
           (
           ([DOV-2009 Matchback].[Order Date]) 
                Between #1/12/2009# And #1/18/2009#
           )
           );
    what's happening here is that the COUNT function is counting all non-null values of that column in the joined result set

    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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