Query not totaling correctly


T

Tara

I have a totals query that is supposed to sum the balance due for all
receipts from any given customer. The field type is currency. Negative
balance due amounts are stored as ($12.00) for example as opposed to positive
balance due amounts which are stored as $12.00. For example Customer 1 has a
balance due on receipt A of $12.00 and they did not pay it when the receipt
was generated. Then they later paid that balance - tracked with a different
receipt - receipt B. The second receipt then shows a negative ($12.00)
balance since that receipt had no balance due (was only a payment on an
earlier balance due). Receipt A shows a total of $12.00 due. Receipt B
shows a ($12.00) balance due. When I run the query these should cancel each
other out and show a total of $0.00 due. However, it doesn't. It shows
$12.00 due. What am I doing wrong? Would storing the negative balance due
amounts in the -$12.00 format rather than the ($12.00) format work better?
Any help is appreciated!
 
Ad

Advertisements

J

Jeff Boyce

Tara

We aren't there. We can't see "how" you are trying to do this.

Please post the SQL statement of the query you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

Tara

Okay, here it is. Thanks for looking at it!

SELECT tblReceipts.CustFirst, tblReceipts.CustLast, tblReceipts.Address,
tblReceipts.City, tblReceipts.Date, tblReceipts.LoavesTaken,
tblReceipts.LoavesReturned, tblReceipts.TotalAmtOwed, tblReceipts.AmtPaid,
tblReceipts.Void, tblReceipts.Business, IIf([tblReceipts].[custfirst] Is
Null,"",[tblReceipts].[custfirst] & " ") & IIf([tblReceipts].[custlast] Is
Null,"",[tblReceipts].[custlast] & " ") & IIf([tblReceipts].[business] Is
Null,"",[tblReceipts].[business]) AS CustName, tblReceipts.CustID,
tblReceipts.[Receipt#], Sum(qryOutstandingTOTAL.BalanceDue) AS
SumOfBalanceDue, tblReceipts.AmtStillOwed
FROM qryOutstandingTOTAL INNER JOIN (tblRates INNER JOIN tblReceipts ON
tblRates.RateID = tblReceipts.Rate) ON qryOutstandingTOTAL.CustID =
tblReceipts.CustID
GROUP BY tblReceipts.CustFirst, tblReceipts.CustLast, tblReceipts.Address,
tblReceipts.City, tblReceipts.Date, tblReceipts.LoavesTaken,
tblReceipts.LoavesReturned, tblReceipts.TotalAmtOwed, tblReceipts.AmtPaid,
tblReceipts.Void, tblReceipts.Business, IIf([tblReceipts].[custfirst] Is
Null,"",[tblReceipts].[custfirst] & " ") & IIf([tblReceipts].[custlast] Is
Null,"",[tblReceipts].[custlast] & " ") & IIf([tblReceipts].[business] Is
Null,"",[tblReceipts].[business]), tblReceipts.CustID,
tblReceipts.[Receipt#], tblReceipts.AmtStillOwed
HAVING (((tblReceipts.Void)=No))
ORDER BY tblReceipts.Business;
 
Ad

Advertisements

T

Tara

I just posted the SQL statement, but it doesn't matter, I found an error in
data entry that was causing the problem. Fixed it and now it's working fine!

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top