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:
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.
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?
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?
