Query Issue returning incorrect information

[lotharious]

I have 2 Querys

Query 1 named tmm

SELECT [Accts Pay].Acct, Sum([Accts Pay].Credit) AS SumOfCredit, Sum([Accts Pay].Debit) AS SumOfDebit
FROM [Accts Pay]
GROUP BY [Accts Pay].Acct
ORDER BY [Accts Pay].Acct;

This returns

Acct	SumOfCredit	SumOfDebit
1	$3,752.13	$2,132.77
2	$38,966.92	$21,296.11
3	$2,831.75	$36.33
8	$23,888.92	$9,084.15
14	$6,016.31	$1,539.72
15	$213.46	$213.46
19	$690.35	$451.02
20	$1,168.06	$0.00
21	$16,729.83	$10,059.04

Query 2 named tmm2

SELECT [Accts Pay].Acct, Sum([Accts Pay].Credit) AS SumOfCredit, Sum([Accts Pay].Debit) AS SumOfDebit
FROM [Accts Pay]
GROUP BY [Accts Pay].Acct
ORDER BY [Accts Pay].Acct;

This returns

Acct	SumOfCredit	SumOfDebit	dif
1	$3,740.95	$2,132.77	$1,608.18
2	$38,926.68	$21,296.11	$17,630.57
3	$2,831.75	$36.33	$2,795.42
8	$23,471.65	$9,084.15	$14,387.50
14	$6,016.31	$1,539.72	$4,476.59
15	$213.46	$213.46	$0.00
19	$690.35	$451.02	$239.33
20	$1,168.06	$0.00	$1,168.06
21	$16,729.83	$10,059.04	$6,670.79

As you can see by the data, that the data returned does not match.

I have a report based off of this data from a seperate query and I get mixed data that matchs partially from the first, and partially from the second.

I am can only assume that the data from tmm is correct since it is querying directly from the table, where tmm2 is querying from tmm.

 

[DotNetGnat]

please repost the query tmm2 as you have missed the dif part..

-DNG

 

[lotharious]

oops heh

SELECT tmm.Acct, tmm.SumOfCredit, tmm.SumOfDebit, [sumofcredit]-[sumofdebit] AS dif
FROM tmm;

 

[lotharious]

Added information

The only time I get different data from the queries is if I have the report open that is mentioned before. Im not sure what is happening or why. But if I dont have the report open then I get matching data with the report.

 

[PHV]

Why by the earth not simply this for tmm2 ?
SELECT Acct, Sum(Credit) AS SumOfCredit, Sum(Debit) AS SumOfDebit, Sum(Credit)-Sum(Debit) AS dif
FROM [Accts Pay]
GROUP BY Acct
ORDER BY Acct;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lotharious]

PHV,
The reason I did the 2 querys was I was getting different data than I had in my report. And I was trying to figure out why.
Im actually using that query now.. which it will not give me correct info if I have the report open.