CODEWITHSUNDEEP
sqlms-access
salisboss
salisboss

Reputation: 139

I am getting an incorrect sum in my Access query?

I have a database that tracks parts loans and payback to multiple organizations in my company. I have a query that for a given customer and part name should show a sum of how many have been loaned out (may have been multiple loans), the sum of how many have been paid back (may have been multiple paybacks) and a calculated field that takes the difference of the two. The sum in RcvdQTY is not calculating correctly. In the example in the screenshot below it should be Rcvd = 72, Paid Back = 72, Balance = 0 but it reads 648, 72, 576

Screenshot of my query below: enter image description here

As requested here is my SQL for the query:

SELECT CustomerT.CustomerProgram, PartsT.PartName, PartsT.IndiaUID, PartsT.NSN, Sum(DiversionT.RcvdQTY) AS SumOfRcvdQTY, Sum(PaybackT.PayAmountParts) AS SumOfPayAmountParts, Sum([RcvdQTY]-[PayAmountParts]) AS BalanceOwed
FROM SupplierT 
INNER JOIN (PayMethodT 
INNER JOIN ((PartsT 
INNER JOIN (CustomerT 
INNER JOIN DiversionT 
ON CustomerT.CustomerID = DiversionT.CustomerID) 
ON PartsT.PartID = DiversionT.PartID) 
INNER JOIN PaybackT 
ON (PartsT.PartID = PaybackT.PartID) AND (CustomerT.CustomerID = PaybackT.CustomerID)) 
ON (PayMethodT.PayMethodID = PaybackT.PayMethodID) AND (PayMethodT.PayMethodID = DiversionT.PayMethodID)) 
ON SupplierT.SupplierID = DiversionT.SupplierID
GROUP BY CustomerT.CustomerProgram, PartsT.PartName, PartsT.IndiaUID, PartsT.NSN
HAVING (((CustomerT.CustomerProgram)="M777 Australia") AND ((PartsT.PartName)="CSD-R"));

Upvotes: 0

Views: 75

Answers (2)

salisboss
salisboss

Reputation: 139

Based on Parfait's help I created two subquerys then created another query that took the outputs and did the final calculation. Thanks to all the help I was able to get the sums correct with the following SQL code:

SELECT SinglePartPaybackSumQ.CustomerProgram, PartsT.IndiaUID, PartsT.NSN, PartsT.PartName, SinglePartSumQ.SumOfRcvdQTY, SinglePartPaybackSumQ.SumOfPayAmountParts, [SumOfRcvdQTY]-[SumOfPayAmountParts] AS BalanceOwed

FROM (PartsT INNER JOIN SinglePartPaybackSumQ ON PartsT.PartID = SinglePartPaybackSumQ.PartID) INNER JOIN SinglePartSumQ ON PartsT.PartID = SinglePartSumQ.PartID;

Upvotes: 1

Parfait
Parfait

Reputation: 107567

Because your undesired results yielding 648, 72, and 576 are all multiples of 72, you likely are facing duplicate amounts from multiple joins. Consequently, when aggregating it sums all those repeat values.

Looking at your Query Design, consider joining two separate aggregate queries for your final expression, BalancedOwed. Below is my guess at your adjusted SQL. Fix column and table names as needed. All other unnecessary tables were removed:

SELECT d_agg.CustomerProgram, d_agg.PartName, d_agg.IndiaUID, d_agg_agg.MSN, 
       d_agg.Total_RcvdQTY, p_agg.Total_PayAmountParts,
       (d_agg.Total_RcvdQTY - p_agg.Total_PayAmountParts) AS BalanceOwed
FROM 
  (SELECT c.CustomerProgram, prt.PartName, prt.IndiaUID, prt.MSN, 
          SUM(d.RcvdQTY) AS Total_RcvdQTY
   FROM (CustomersT c
   INNER JOIN DiversionT d
      ON c.CustomerID = d.CustomerID)
   INNER JOIN PartsT prt
      ON prt.PartID = d.PartID    
   GROUP BY c.CustomerProgram, prt.PartName, prt.IndiaUID, prt.MSN
  ) d_agg

INNER JOIN
  (SELECT c.CustomerProgram, prt.PartName, prt.IndiaUID, prt.MSN, 
          SUM(pyb.PayAmountParts) AS Total_PayAmountParts
   FROM (CustomersT c
   INNER JOIN PaybackT pyb
      ON c.CustomerID = pyb.CustomerID)
   INNER JOIN PartsT prt
      ON prt.PartID = pyb.PartID    
   GROUP BY c.CustomerProgram, prt.PartName, prt.IndiaUID, prt.MSN
  ) p_agg

 ON d_agg.CustomerProgram = p_agg.CustomerProgram
 AND d_agg.PartName = p_agg.PartName
 AND d_agg.IndiaUID = p_agg.IndiaUID
 AND d_agg_agg.MSN = p_agg.MSN

WHERE d_agg.CustomerProgram = 'M777 Australia'
AND d_agg.PartName = 'CSD-R'

Upvotes: 1

Related Questions