Prev: Sum of IIF
Next: Grand totals in reports
From: StuJol on 2 Jul 2008 16:25
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?
From: KARL DEWEY on 2 Jul 2008 19:21
Try using Mod 4 function = 0 for grouping.
--
KARL DEWEY
Build a little - Test a little


"StuJol" wrote:

> 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?
From: StuJol on 2 Jul 2008 19:40
Thanks Karl,

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

thanks


"KARL DEWEY" wrote:

> Try using Mod 4 function = 0 for grouping.
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "StuJol" wrote:
>
> > 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?
From: John Spencer on 3 Jul 2008 07:28
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

StuJol wrote:
> 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?
 | 
Pages: 1
Prev: Sum of IIF
Next: Grand totals in reports