union query for totals - not calculating averages correctly?

jtkjames

Registered User.
Local time
Today, 09:53
Joined
Jul 12, 2010
Messages
46
hello all

got a bit of a puzzle

i have set up a union query to give an extra row at the bottom of a cross tab query containing the averages of the above columns, with the last column being a total count of the above

the code is:

Code:
SELECT DISTINCTROW tblSold.[MOD] AS Model, tblSold.Description AS Derivative, Avg(tblSold.[RESALE AMT]) AS [Average Resale Amount], Avg(tblSold.[CAP Clean]) AS [Average CAP Clean], Avg(tblSold.[%cap clean]) AS [Average % CAP Clean], Avg(tblSold.MILEAGE) AS [Average Mileage], Count(tblSold.[REG NO]) AS [Count]

FROM (tblSold LEFT JOIN tblOriginalSource ON tblSold.[REG NO] = tblOriginalSource.[REG NO]) LEFT JOIN tblDealerNames ON tblOriginalSource.[ORIG SOURCE DLR] = tblDealerNames.[DLR CODE]

WHERE (((tblSold.[STATUS DT])>=[Start Date (DD/MM/YYYY):] And (tblSold.[STATUS DT])<=[End Date (DD/MM/YYYY):]))

GROUP BY tblSold.[MOD], tblSold.Description

HAVING (((tblSold.[MOD]) Like "*" & [Choose a model (Leave blank for all):]));
 
UNION 
 
SELECT "Total", "", avg([RESALE AMT]),  avg([CAP Clean]), avg([%cap clean]), avg([MILEAGE]), count([REG NO])

FROM (tblSold)

WHERE (((tblSold.[STATUS DT])>=[Start Date (DD/MM/YYYY):] And (tblSold.[STATUS DT])<=[End Date (DD/MM/YYYY):]))

HAVING (((tblSold.[MOD]) Like "*" & [Choose a model (Leave blank for all):]));

however upon checking the average calculations in excel, it appears they are inaccurate. the count is fine, but the avg functions seem to be working off a different set of data than the crosstab presents to the user.

any ideas why?

specifically the fields are about cars. ignore the joins, they are just to lookup better text rather than the dealer code.

Code:
MOD, Description = text fields
REG NO = registration number (we use this as primary key for count purposes) aka license plate number in america
RESALE AMT, CAP Clean = monetary figure
%cap clean = percentage figure

i suspect that the avg function is calculating an average from the whole filtered source of data rather than an average of the summary averages presented by the crosstab.

but my rather distant memories of mathematics make me think that the "average of an average" is equal to just a single "average" unless we use root mean square - but i think thats beyond the scope of access!

so, experts out there! any ideas? :D
 
upon further excel testing it appears that my maths is crap and my fears are right

the avg aggregate functions are averaging the whole dataset and not just the summary (groupby) figures in the column above.

is it possible to union with a dupe xtab query with the same parameters?
 

Users who are viewing this thread

Back
Top Bottom