G
Gamliel
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
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