Sort by group total

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following fields in my report:
Brand
Vendor
Month
Leads
Enrolls
Lead to Enroll Rate (calculated field)

My top group is Brand, and under that I have a vendor grouping, and the
detail section breaks out the data by month. My goal is to have the vendor
groups sorted by the Lead to Enroll Rate (the total lead to enroll rate
calculated in the vendor footer). I don't even know where to start, so any
suggestions would be greatly appreciated.
 
Can you perform the calculation in a query first and use the query as the
report's data source? The field will then be available just like any other
field and you can group on it in any order you choose.
 
If I do that, it seems to take a very long time for the query to run. Here
is what I have:

((SELECT SUM(Enrolls) FROM TblResponse AS x where (x.Brand =
TblResponse.Brand) and (x.Vendor = TblResponse.Vendor))/(SELECT SUM(Leads)
FROM TblResponse AS y where (y.Brand = TblResponse.Brand) and (y.Vendor =
TblResponse.Vendor))) AS EnrollRate


How can I make this run faster. I have all of the relevant fields indexed.
 
How many duplicate records are being calculated? In other words, is the data
such that the same calculation is being performed multiple times because
there are many records with the same brand and vendor? If this is the case,
you may be able to speed things up by creating a temporary table with a list
of distinct brand and vendor combinations. Then calculate the rate for each
record (once) and link the table to your other data.

Also, is the data very dynamic? Can you calculate these values ahead of time?

If I do that, it seems to take a very long time for the query to run. Here
is what I have:

((SELECT SUM(Enrolls) FROM TblResponse AS x where (x.Brand =
TblResponse.Brand) and (x.Vendor = TblResponse.Vendor))/(SELECT SUM(Leads)
FROM TblResponse AS y where (y.Brand = TblResponse.Brand) and (y.Vendor =
TblResponse.Vendor))) AS EnrollRate

How can I make this run faster. I have all of the relevant fields indexed.
Can you perform the calculation in a query first and use the query as the
report's data source? The field will then be available just like any other
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
Great idea, Thanks for all the help kingston!

kingston via AccessMonster.com said:
How many duplicate records are being calculated? In other words, is the data
such that the same calculation is being performed multiple times because
there are many records with the same brand and vendor? If this is the case,
you may be able to speed things up by creating a temporary table with a list
of distinct brand and vendor combinations. Then calculate the rate for each
record (once) and link the table to your other data.

Also, is the data very dynamic? Can you calculate these values ahead of time?

If I do that, it seems to take a very long time for the query to run. Here
is what I have:

((SELECT SUM(Enrolls) FROM TblResponse AS x where (x.Brand =
TblResponse.Brand) and (x.Vendor = TblResponse.Vendor))/(SELECT SUM(Leads)
FROM TblResponse AS y where (y.Brand = TblResponse.Brand) and (y.Vendor =
TblResponse.Vendor))) AS EnrollRate

How can I make this run faster. I have all of the relevant fields indexed.
Can you perform the calculation in a query first and use the query as the
report's data source? The field will then be available just like any other
Click to expand...
[quoted text clipped - 13 lines]
calculated in the vendor footer). I don't even know where to start, so any
suggestions would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top