Results 1 to 4 of 4
  1. #1
    Degs29 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2013
    Posts
    38

    Query returning sums many times actually sum.

    I have a table/form with fields "Hours", "Employee", "PartCode", "OperationCode", and "Quantity". For each entry, we enter the number of hours a particular employee spends on a certain operation associated with a particular component, and the number of parts he/she produced in that time frame. There will be thousands of these entries in time.

    Basically what I want is for my query to tally total Hours and total Quantity based on PartCode and OperationCode (in the future I may also break it down by Employee, but not today). Here's my query:

    SELECT [PartCodeList].PartCode AS PartCode, [OperationCodeList].OperationCode AS OperationCode, SUM([WorkLogHoursSub].Hours) AS Hours, Sum([WorkLogHoursSub].Quantity) AS Quantity
    FROM PartCodeList, OperationCodeList, WorkLogHoursSub
    WHERE (([WorkLogHoursSub].PartCode)=PartCodeList.PartCode) And (([WorkLogHoursSub].OperationCode)=OperationCodeList.OperationCode)
    GROUP BY [PartCodeList].PartCode, [OperationCodeList].OperationCode;

    I've entered the following information in the corresponding fields in the form as a test (PartCode / OperationCode / Hours / Quantity):
    • H1 / B / 2 / 2
    • H1 / C / 3 / 4
    • H1 / D / 20 / 40
    • H2 / B / 8 / 12
    • H1 / C / 6 / 9
    • H3 / D / 8 / 40

    However, that query returns strange results for total hours and total quantity in the query. Results are below (Code[combined] / CorrectHours / ActualHours / CorrectQty / ActualQty):
    • H1B / 2 / 64 / 2 / 64
    • H1C / 9 / 243 / 13 / 351
    • H1D / 20 / 480 / 40 / 960
    • H2B / 8 / 256 / 12 / 384
    • H3D / 8 / 192 / 40 / 960

    Strangely enough, although the correct total hours for H2B and H3D are identical, the query returns differing results. On the flip side, the correct total quantity for H1D and H3D is 40, and the query returned identical 960s for both of them....

    Whoa, just figured something out. When the OperationCode is "B", it's multiplying the correct total by 32. When the code is "C", it's multiplying by 27. And when the code is "D", it's multiplying by 24. Any idea why that's happening?

    EDIT: "I" is multiplying by 1 and so getting the correct results.... lol



    EDIT2: Only one PartCode is associated with the "I" OperationCode, whereas 32 PartCodes are associated with the "B" OperationCode, 27 with "C", and 24 with "D". So it appears the query is multiplying by the number of times the OperationCode appears in the OperationCodeList table, regardless of whether or not it's associated with the correct PartCode.

    EDIT3: Any idea how to fix that?

    SOLUTION: I changed the query so it also pulls the PartCode from the OperationCodeList, rather than a separate table. This has solved the problem. Fixed query:

    SELECT [OperationCodeList].PartCode AS PartCode, [OperationCodeList].OperationCode AS OperationCode, SUM([WorkLogHoursSub].Hours) AS Hours, Sum([WorkLogHoursSub].Quantity) AS Quantity
    FROM OperationCodeList, WorkLogHoursSub
    WHERE (([WorkLogHoursSub].PartCode)=OperationCodeList.PartCode) And (([WorkLogHoursSub].OperationCode)=OperationCodeList.OperationCode)
    GROUP BY [OperationCodeList].PartCode, [OperationCodeList].OperationCode;

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you do a join on other tables, you can get mutiple records.
    I would try writing to 1 'working' table, then modifying fields with update queries, in it so you cant get duplicates BUT see what is happening with the data.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The query references 3 tables but there is no JOIN clause. A JOIN clause is more efficient than WHERE for relating records.

    I presume PartCodeList and OperationCodeList are just lookup tables.

    Set links in the query - what happens?

    I don't see calcs in the query for ActualHours and ActualQty.

    Don't need to use alias on native fields if you aren't changing the name.

    EDIT: Just saw your post edits and ranman's post. Yep, that's the issue. Need a dataset of all possible OperationCode and PartCode pairs to include in the query with compound key join.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Degs29 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2013
    Posts
    38
    Thanks guys, I managed to work my way through that just by writing it down. I'm self-taught on Access, so I don't know much about this stuff!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to do Sum of Sums in Query
    By braveali in forum Access
    Replies: 4
    Last Post: 01-19-2014, 10:32 PM
  2. Query help with Sums
    By dhogan444 in forum Queries
    Replies: 1
    Last Post: 08-10-2012, 02:19 PM
  3. Query that sums data
    By accessnovice78 in forum Queries
    Replies: 3
    Last Post: 04-12-2012, 11:46 AM
  4. Replies: 1
    Last Post: 03-23-2012, 09:45 AM
  5. query that sums the data for me
    By citygov in forum Queries
    Replies: 4
    Last Post: 08-18-2011, 03:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums