Results 1 to 4 of 4
  1. #1
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63

    Sum from 2 Tables Results Wrong Values

    Hello, I am having trouble summing two different amounts from two different tables (BILLs & BillPayment).



    I am trying to sum All Bills Amount in 2nd column and All BillPayment Amount in 3rd column, based on CustomerID in 1st column, but getting wrong results. Please help. Sample DB file is attached
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Since you want to GROUP on customer, do two queries that aggregate each table on the customer then join those two queries.
    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.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    This is one of the problems you get when you split transactions into different tables since it is not strictly normalised. Suggest you combine the tables, then it becomes a simple matter. You may have the odd field required for one transaction but not the other - in your example ModeofPayment and BillDetails could be combined under a 'Details' or TranDescription' column, leaving InstrumentNo blank for bills - and you would need a column to indicate transaction type (using positive and negative values to determine the type is not normally sufficient - what if you issue a credit note or a refund for example?)

    The reason you have a problem is because you have multiple payments against bills - so with three payments against a single bill, your bill value is being summed 3 times.

    To keep your tables the way the way you have them.

    1. Change your existing query by a) adding in the bill payment bill id and grouping on it and b) change the sum for billamount to a group by (sum of bill payments is ok). This will give the amounts by bill. You may think you don't need the bill id, and in your example data you don't - but that is because every bill amount is different. If you had two bills for the same amount for the same customer, you would have the same problem you have at the moment.
    2. create another query based on this query and group on customerid, then sum on bill amount, payment amount and Balance.

    There are other ways, but they all involve using two or more queries, rather than the one required for a normalised approach (and they will be slower if you have large amounts of data)

    I note you are using lookups in your table design. Although OK for messing around and quickly trying out different scenarios I strongly recommend you remove the lookups. They will cause you problems down the line.

  4. #4
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    Thanks, splitting into two queries solved the problem.

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

Similar Threads

  1. Replies: 4
    Last Post: 06-20-2017, 12:53 PM
  2. Search form getting wrong results
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 08-22-2013, 06:32 PM
  3. Sum Query Delivers wrong results
    By rbf in forum Queries
    Replies: 5
    Last Post: 03-30-2013, 03:18 PM
  4. Calculation in Query giving wrong results
    By dargo72 in forum Queries
    Replies: 11
    Last Post: 11-07-2012, 05:39 AM
  5. Expression gives wrong results
    By newtoAccess in forum Queries
    Replies: 22
    Last Post: 12-03-2010, 12:21 AM

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