Totals query can't count

[kbestle]

I have a Select query that returns 15 records. The three fields are Year, Qty, Amount. When I convert this to a Totals query grouping on Year, Averaging the Amount and Counting the Qty the query comes back with a Count of 16. How is this possible?

TIA

Kent

 

[PHV]

Did the Select query use the DISTINCT predicate ?

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

 

[kbestle]

Changeing the SQL statement from Select to Select Distinct did not change the results in the Totals query. It did group the Select query to 4 records based on 4 different Amounts.

 

[PHV]

So, I rephrase my question as you didn't understood it:
please, post the SQL code of the original Select query and the SQL code of the Totals query.

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

 

[kbestle]

My query is actually more involved than I originally stated. I did not want to complicate the question with possible erroneous information. Anyway here is the Totals SQL:

SELECT dbo_HistoryAll.PartNo, Year([TranDate]) AS Yr, Count(dbo_HistoryAll.Qty) AS CountOfQty, Avg(dbo_HistoryAll.TranAmt) AS AvgOfTranAmt
FROM dbo_HistoryAll
WHERE (((dbo_HistoryAll.DocType)="fr") AND ((dbo_HistoryAll.CurAssn)="ac"))
GROUP BY dbo_HistoryAll.PartNo, Year([TranDate])
HAVING (((dbo_HistoryAll.PartNo)="ag44a") AND ((Year([TranDate]))=2004) AND ((Avg(dbo_HistoryAll.TranAmt))>0));

Here is the Select SQL:

SELECT dbo_HistoryAll.PartNo, Year([TranDate]) AS Yr, dbo_HistoryAll.Qty, dbo_HistoryAll.TranAmt
FROM dbo_HistoryAll
WHERE (((dbo_HistoryAll.PartNo)="ag44a") AND ((Year([TranDate]))=2004) AND ((dbo_HistoryAll.TranAmt)>0) AND ((dbo_HistoryAll.DocType)="fr") AND ((dbo_HistoryAll.CurAssn)="ac"));

Thanks

 

[PHV]

Your Totals query doesn't aggregate the same rows as those returned by your Select query.
To get a consistent result:
SELECT dbo_HistoryAll.PartNo, Year([TranDate]) AS Yr, Count(dbo_HistoryAll.Qty) AS CountOfQty, Avg(dbo_HistoryAll.TranAmt) AS AvgOfTranAmt
FROM dbo_HistoryAll
WHERE (((dbo_HistoryAll.PartNo)="ag44a") AND ((Year([TranDate]))=2004) AND ((dbo_HistoryAll.TranAmt)>0) AND ((dbo_HistoryAll.DocType)="fr") AND ((dbo_HistoryAll.CurAssn)="ac"))
GROUP BY dbo_HistoryAll.PartNo, Year([TranDate])


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