Averaging a query with a subquery

  • Thread starter Thread starter Gamliel
  • Start date Start date
G

Gamliel

B'H

This should be easy: I am using a subquery to make a
simple calculation within a query. Now I want to see the
average of the column that resulted. Is there a way to do
that?

Thanks very much.

Gamliel
 
Check Access Help on the Avg function in an Aggregate Query.

If that doesn't help, I think you need to provide more info. about your
Table structure, your current Query and what you want to get. If possible,
provide a small sample of data and the result you want from the sample.

BTW, some potential respondents don't like to reply to "should be easy"
question since if it is easy, then you should work it out. Since you
haven't work it out, how would you know it is easy?

Some requirements may look simple but it may not be that easy when trying to
work it out in Queries.
 
Here's the example of what I am trying to do:

I have one column of numbers that stands for the number
of visitors (1) to different sites. I have another column
with a rate - also for those same sites (2) (let us say a
hot lead rate). I want to calculate the average of the
second column - but weighted by the number of visitors
(1).

To do that, I create another column (3) defined by a
subquery that summs up all the values in column (1). Then
I create a column (4) where I put the ratio of (1) to
(3). Now I create another column (5) equal to (2)*(4) -
the rate adjusted by the ratio of the site visits to
total visits.

Now I would like to create a report that has the average
rate (weighted) for all the sites. The way to do that is
sum all values in column (5) divided by sum of all values
in column (4).

The problem is that it is impossible to aggregate such a
query in Access (each row in the query is a calculation).

Is there a straightforwrd way to get around this?

Thanks so much!!! Gamliel
 
Gamliel,

I created a table that contains three fields, SiteID, Visitor, Rate. I
filled these fields with values and then tested the following SQL, which I
think accomplishes what you want.

SELECT SiteID
, [visitors]/DSum("Visitors","tbl_Visitors")*[Rate] AS
WeightedAverage
FROM tbl_Visitors;

Use the DSUM function to get the sum of the number of visitors across all
sites. Then use that in your query. You could also probably use:

SELECT V.SiteID
, V.Visitors/S.TotVisitors*V.Rate as WeightedAverage
FROM tbl_Visitors as V,
(SELECT SUM(Visitors) as TotVisitors
FROM tbl_Visitors) as S

I'm not sure which of these will be faster. I think the DSUM function will
get computed for each record in the table (don't quote me on that), but am
pretty sure the second method will only compute the TotVisitors value once
for the entire query.

HTH
Dale
 
Back
Top