Group by week, area, value

  • Thread starter Thread starter Sheryl Bradley
  • Start date Start date
S

Sheryl Bradley

Have fields

ListDate
Area
Price

Want to generate output:

Week1:
$0-250,000 $250,001-500,000 etc.
Area1 $x $y
Area2 $z $q
Area3


Week2:
$0-250,000 $250,001-500,000 etc.
Area1
Area2
Area3
....

Basically looking to group by week, then by area, and
within area sum total by 4 different price ranges. The
price ranges are the most puzzling part for me ...

Thanks a million!

Sheryl Bradley :)
 
Hi,


A Crosstab with Partition can be a start (take a look at Partition,
in the help file).


Hoping it may help,
Vanderghast, Access MVP
 
Create a table of price ranges:
tblPriceRanges
================
RangeMin
RangeMax
RangeTitle

Add this to your query and set the criteria under the Price column to
Between [RangeMin] and [RangeMax]
Add RangeTitle to the grid and then make a Crosstab query where:
Field: WeekOf: Format([ListDate],"yyyy ww")
Total: Group By
Crosstab: Row Heading

Field: Area
Total: Group By
Crosstab: Row Heading

Field: RangeTitle
Total: Group By
Crosstab: Column Heading

Field: Price
Total: Sum ?
Crosstab: Value
 
Back
Top