Grouping

  • Thread starter Thread starter StuJol
  • Start date Start date
S

StuJol

i have an access2003 report which displays data like this

week amount
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10

im trying to group records every 4 weeks so i can get a sum. so weeks 1-4 =
sum of 40, weeks 5 - 8 would = sum 40

does anyone know how this can be done?
 
Thanks Karl,

Can you please explain in a little more detail? not quite sure what u mean.

thanks
 
Use a calculated expression ([Week]-1) \ 4 to group on

SELECT ([Week]-1) \ 4 as WeekGroup
, Sum(Amount) as TheTotal
FROM [Your Table]
GROUP BY ([Week]-1) \ 4

In the query design view (the grid)
-- add the table
-- Type the following in the first field "cell:
WeekGroup: ([Week]-1) \ 4
-- Add the Amount field
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under the Amount field


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top