Results 1 to 11 of 11
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    How to avoid inflated total in query?

    Hi, i have a database with 3 tables ORDER_TRANSACTION, ORDER_TRANSACTION_DETAIL, ORDER_PAYMENT.
    See the db structure on attachment below.

    Click image for larger version. 

Name:	DbStructure.PNG 
Views:	19 
Size:	15.1 KB 
ID:	34813

    When i have order which is associated with many payments, and try to run


    a query to display the TOTAL_DUE and TOTAL_AMOUNT_PAID for that specific order, i get an inflated total
    for the TOTAL_DUE.


    Example: in illustration 2 below, we have one order which total is amounted to 195000. Then we have
    three payments associated to that specific order. Total payment for that order is also 1950000.

    Click image for larger version. 

Name:	tables.jpg 
Views:	19 
Size:	111.5 KB 
ID:	34814

    Now i am trying to run a query to get these two totals then compute the balance, but it seem like the totalDue
    is multiplied by the number of entry in the ORDER_PAYMENT table.

    Click image for larger version. 

Name:	query.jpg 
Views:	20 
Size:	21.8 KB 
ID:	34815

    below is the query:


    Code:
    SELECT Sum(ORDER_TRANSACTION_DETAIL.ProdPrice*ORDER_TRANSACTION_DETAIL.QuantityOrdered) AS TotalDue, SUM(ORDER_PAYMENT.PaymentAmount) AS TotalPaid, (TotalDue - TotalPaid) AS Balance
    FROM (ORDER_TRANSACTION INNER JOIN ORDER_PAYMENT ON ORDER_TRANSACTION.OrderID_PK = ORDER_PAYMENT.OrderID_FK) INNER JOIN ORDER_TRANSACTION_DETAIL ON ORDER_TRANSACTION.OrderID_PK = ORDER_TRANSACTION_DETAIL.OrderID_FK
    GROUP BY ORDER_PAYMENT.OrderID_FK, ORDER_TRANSACTION_DETAIL.OrderID_FK;

    Can someone assist me??
    Attached Files Attached Files
    Last edited by ezybusy; 07-23-2018 at 12:48 PM. Reason: add sample db

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,157
    The TotalDue is being multiplied by your 3 payments. That is because you have joined the payments to the orders, which replicates the amount.
    You need to do these sums either in separate queries then join them into a final result query , or use a SubQuery.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    I also thought of subquery but had no idea as to how to go about it.
    Can you help me out with the subquery??

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,157
    I'm not at my PC at the moment but from the query you have, add this, and remove the fields in it from your original query:

    Code:
    (SELECT OrderID_FK, Sum(ORDER_TRANSACTION_DETAIL.ProdPrice*ORDER_TRANSACTION_DETAIL.QuantityOrdered) AS TotalDue,
    FROM ORDER_TRANSACTION_DETAIL
    Group By ORDER_TRANSACTION_DETAIL.OrderID_FK) As TD
    You might need to play around with it a bit, or simply store this as another query and add it to your existing one after removing the relevant duplicated data.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    I still cant get it.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,157
    Can you post up a sample db with the tables and some sample data.
    Won't need a lot.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    I agree with Minty that a sample db would be helpful.

    As I see the basic issue is, as Minty said, you have joined Payment with Order. These are separate concepts.
    Consider, I buy 1 article for X amount.

    When I pay, I may use Cash or Card or some combination of each. I could have 1 payment or several. The only "thing" relating Order and Payment, is that you want to ensure that for any "Sold" item, you collect the appropriate total amount from the Customer.


    Subquery info and examples by Allen Browne

    Good luck.

  8. #8
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Alright guys, i am out, so will post a sample db when i return home.
    Thanks in advance

  9. #9
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Guys i have attached a sample db at the end of to the original post. Kindly have a look at it.

  10. #10
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,157
    Please see the attached, this has three queries:
    OrderTotals
    Code:
    SELECT ORDER_TRANSACTION.OrderID_PK, Sum([ProdPrice]*[QuantityOrdered]) AS TotalDueFROM ORDER_TRANSACTION INNER JOIN ORDER_TRANSACTION_DETAIL ON ORDER_TRANSACTION.OrderID_PK = ORDER_TRANSACTION_DETAIL.OrderID_FK
    GROUP BY ORDER_TRANSACTION.OrderID_PK;
    PaymentTotals
    Code:
    SELECT ORDER_PAYMENT.OrderID_FK, Sum(ORDER_PAYMENT.PaymentAmount) AS TotalPaidFROM ORDER_PAYMENT_MODE INNER JOIN ORDER_PAYMENT ON ORDER_PAYMENT_MODE.ModeID_PK = ORDER_PAYMENT.ModeID_FK
    GROUP BY ORDER_PAYMENT.OrderID_FK;
    Finally those two joined to provide a balance
    TransactionBalances
    Code:
    SELECT OrderTotals.OrderID_PK, OrderTotals.TotalDue, PaymentTotals.TotalPaid, [TotalDue]-[TotalPaid] AS BalanceFROM OrderTotals LEFT JOIN PaymentTotals ON OrderTotals.OrderID_PK = PaymentTotals.OrderID_FK;
    Newsampledb_withQueries.zip
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Hi Minty.
    May God bless you. Thanks a lot

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

Similar Threads

  1. Replies: 14
    Last Post: 08-26-2020, 05:26 PM
  2. Replies: 5
    Last Post: 04-11-2018, 11:31 AM
  3. Replies: 3
    Last Post: 03-24-2017, 01:30 PM
  4. Replies: 16
    Last Post: 01-19-2017, 08:14 AM
  5. Replies: 3
    Last Post: 02-07-2016, 10:48 AM

Tags for this Thread

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