Grouping Query/Report by a range of values

[Kronix]

Say I want to create a query or report with average prices for different machine models. However, some machines with the same model can have a different weight. So I want it to group average prices according to a range of values for each model.

So I would have a row for each model that shows the average for all models, and below that the averages for each range of weights.

Model1             averages for all Model1 machines
   0-500kg         averages for this weight range of Model1 machines
   501-1000kg      averages for this weight range of Model1 machines
   1001-2000kg     averages for this weight range of Model1 machines
Model2             averages for all Model2 machines
   0-500kg         averages for this weight range of Model2 machines
   501-1000kg      averages for this weight range of Model2 machines
   1001-2000kg     averages for this weight range of Model2 machines

I only know how to group according to the grouping function in design view, but that only groups according to specific values, not ranges.

 

[Minty]

You ideally need to set up your ranges in a table then use that to drive the grouping.
Have a read here http://allenbrowne.com/ser-58.html for some good pointers.

 

[arnelgp]

if this will work for you Crosstab Query with
Partitioning.

TRANSFORM Avg(Machine.MachineWeight) AS AvgOfMachineWeight
SELECT Machine.MachineModel
FROM Machine
GROUP BY Machine.MachineModel
PIVOT Partition([MachineWeight],1,2000,500);

 

[Kronix]

Thanks.

I have a button on a form that opens a query window based on values in the form. When I change values on the form I want the button to update the query window. I'm using the macros and the refresh options don't do it. The only solution I've found so far is to have the macro always close the query window before opening it. Even though this works, I'm thinking there must be an easier way. This also makes it ask me to save changes if I have edited the query design which I would rather it not do.

 

[arnelgp]

add to your Macro:


Requery
Control Name: <leave blank>