Grouping

[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?

 

[KARL DEWEY]

Try using Mod 4 function = 0 for grouping.

 

[StuJol]

Thanks Karl,

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

thanks

 

[John Spencer]

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