Incorrect results from query (1 Viewer)

BobNTN · Feb 25, 2008

Thought this thing was working great.
Seems this is happening:
If the sum of credits exceed the sum of charges, the query doubles the sum of charges.

The query-

SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, nz([SumOfCharges],0)-nz([SumofCredits],0) AS RunBalance, TblCustInfo.Herbie
FROM (TblCustInfo LEFT JOIN TblCharges ON TblCustInfo.CID=TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID=TblPayments.CID
GROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, TblCustInfo.Cancel, TblCustInfo.Herbie
HAVING (((TblCustInfo.Cancel)="n"));

When I run the query, the SumofCharges calc is the culprit since it shows in that column. I can't dup it in the SumOfCredits, but I would suspect it will also do it somewhere down the line since they are virtually the same.

HELP !!!!

BobNTN · Feb 25, 2008

More testing
It appears that once the credit sum reaches the charges sum, it adds the sum of charges again for each credit entry there is.

Tested again. works the same way in reverse if there is a credit, it will keep adding in the same manner.
Ok what gives guys ?????????????????

Jon K · Feb 26, 2008

Try these three queries.

qryTotalCharges:-
SELECT TblCharges.CID, Sum(TblCharges.Chargeamt) AS SumOfChargeamt
FROM TblCharges
GROUP BY TblCharges.CID;

qryTotalPayments:-
SELECT TblPayments.CID, Sum(TblPayments.Creditamt) AS SumOfCreditamt
FROM TblPayments
GROUP BY TblPayments.CID;

qryBalance:-
SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT,
Nz((Select SumOfChargeAmt from qryTotalCharges where CID=TblCustInfo.Cid)) +0 AS SumOfCharges,
Nz((Select SumOfCreditAmt from qryTotalPayments where CID=TblCustInfo.Cid))+0 AS SumOfCredits,
SumOfCredits-SumOfCharges AS Balance, TblCustInfo.Herbie
FROM TblCustInfo
WHERE (((TblCustInfo.Cancel)="n"));

Run the third query.

Note: Correlated subqueries are inefficient. Running the query may take time if the tables are large.
.

BobNTN · Feb 26, 2008

Not sure I understand why 3 instead of 1 query ?

Yes, that seems to work - however, it DOES take a while to open the balance sheet.
Not to mention having to use 3 different queries for my Debit/credit report.
Also returns a zero balance which don't want since I use the same query for my invoice reports (hence the additional fields)
So why does the 3 setup work and the one doesn't ?

Jon K · Feb 26, 2008

Maybe this can speed up processing a little:-

Change the two totals queries into make-table queries to create two temporary tables.
In the 3rd query, reference the temporary tables instead of the two totals queries.
Run the first two queries before running the 3rd query.

The one-query approach doesn't work because the Charges table and the Payments table are not in a 1-to-1 relationship. When they are put in a query, the records will multiply.

I don't understand the rest of the issues. My 3rd query should return the same fields as your original query does.
.

BobNTN · Feb 26, 2008

Jon K said:
The one-query approach doesn't work because the Charges table and the Payments table are not in a 1-to-1 relationship. When they are put in a query, the records will multiply.
Makes no sense to me. One to one or one to many. If there are 2 entries for $10 each for a specific CID, it can only add the two. Seems then it would depend on HOW it is told to add.

I don't understand the rest of the issues. My 3rd query should return the same fields as your original query does.
.

I use the same query calcs to print invoices depending on the billing category and don't print an invoice for zero balances.
Also, for a debit credit report, it would take all three queries.

Tried the make table way. Might cut it down 3 to 5 seconds but that is just running the third query for the balance report. The make table queries would also have to be re-ran every time a charge or credit is input therefore increasing the time even more plus code to suppress the pop up screens.
There's got to be an "exotic" way of making the single query work. I hope. Got invoices to print in a couple of days.

Jon K · Feb 26, 2008

I thought you had learned the multiplying effect when you put them in your original one query.
.

BobNTN · Feb 26, 2008

Actually, my original plan was to store calculated balances and was advised, and rightly so, to do it this way.
This particular query is a result of a lot of help on here.
So I am really confused.
I have my sister using the db in real world - didn't know about this problem until today.
If there just isn't any way to make this work, I'll have to do a lot of rework downstream. But alas......

Since I am going to have to do some changes, two questions -

Is there an easy way to get only the positive balances at the report ?
Can one use 3 queries in one report ?

I really appreciate all your help.

pbaldy · Feb 26, 2008

I don't have a copy of your db that has that last table you added. Can you post or email me a sample db?

pbaldy · Feb 26, 2008

I sort of agree with Jon but would do the third query differently, which I think will make it faster (joins instead of subqueries). I've got 3 queries:

qryTotalCharges
SELECT TblCharges.CID, Sum(TblCharges.Chargeamt) AS SumOfChargeamt
FROM TblCharges
GROUP BY TblCharges.CID;

qryTotalPayments
SELECT TblPayments.CID, Sum(TblPayments.Creditamt) AS SumOfCreditamt
FROM TblPayments
GROUP BY TblPayments.CID;

final query
SELECT TblCustInfo.CID, TblCustInfo.Name, nz([SumOfChargeamt],0) AS Charges, nz([SumOfCreditamt],0) AS Payments, nz([SumOfChargeamt],0)-nz([SumOfCreditamt],0) AS RunBalance
FROM (TblCustInfo LEFT JOIN qryTotalCharges ON TblCustInfo.CID = qryTotalCharges.CID) LEFT JOIN qryTotalPayments ON TblCustInfo.CID = qryTotalPayments.CID;

Jon K · Feb 26, 2008

Why didn't I think of Left Joins! I tried Inner Joins, failed and changed to subqueries.

To get rid of zero balances:

SELECT TblCustInfo.CID, TblCustInfo.Name, nz([SumOfChargeamt],0) AS Charges, nz([SumOfCreditamt],0) AS Payments, nz([SumOfChargeamt],0)-nz([SumOfCreditamt],0) AS RunBalance
FROM (TblCustInfo LEFT JOIN qryTotalCharges ON TblCustInfo.CID=qryTotalCharges.CID) LEFT JOIN qryTotalPayments ON TblCustInfo.CID=qryTotalPayments.CID
WHERE nz([SumOfChargeamt],0)-nz([SumOfCreditamt],0)<>0;

This query should run much faster than mine.
.

Jane221 · Feb 26, 2008

I saw two different syntaxes of Nz() in this thread.

Nz(...)+0
Nz(..., 0)

Is there any difference between them?

Thank you!

Jon K · Feb 26, 2008

Jane221 said:
I saw two different syntaxes of Nz() in this thread.

Nz(...)+0
Nz(..., 0)

Is there any difference between them?

Thank you!

When used alone, both syntaxes will convert a null value to zero. But the data types returned are different.

The first syntax returns a numeric 0 (you can see that the whole column is right-aligned.)
The second syntax returns text (the whole column is left-aligned.)

When used on two fields for numeric calculations, there is no need to add the 0. You can simply use:
Nz([FieldA]) + Nz([FieldB]).

The second argument in the Nz() function is often used on a text field to convert a null value to a text string e.g.
Nz([FieldC],"N/A")
.

BobNTN · Feb 26, 2008

I really appreciate the help guys.
I knew you would come through.

Now is there a way I can sort records in a report without doing so in the query ?
I need my invoices in Zip order.

pbaldy · Feb 26, 2008

Sure; look at Sorting and Grouping in the report. That's actually where you want to do it anyway.

BobNTN · Feb 26, 2008

Thanks once again Paul.

Hey, I may come to Vegas this summer.
Any good deals only the locals know about ?

pbaldy · Feb 26, 2008

How would I know? I live 400 miles away!

I'll ask the people in our office there if they know about anything. Let me know when, as I go down there every month or two.

Incorrect results from query (1 Viewer)

BobNTN

Registered User.

BobNTN

Registered User.

Jon K

Registered User.

BobNTN

Registered User.

Jon K

Registered User.

BobNTN

Registered User.

Jon K

Registered User.

BobNTN

Registered User.

pbaldy

Wino Moderator

pbaldy

Wino Moderator

Jon K

Registered User.

Jane221

New member

Jon K

Registered User.

BobNTN

Registered User.

pbaldy

Wino Moderator

BobNTN

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread