sum data in a range?

[pemt]

hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
.. .
.. .
.. .
100 96
.. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

 

[Steve Schapel]

Pemt,

Make a query based on this table.

In the query design grid, in the Field row of the first column, enter like
this:
Hundreds: ([id]-1)\100+1
In the second column, put the score field.
Make this into a Totals query (select Totls from the View menu.
In the Totals row in the grid, leave it set to 'Group By' in the 1st column,
and in the 2nd column put 'Sum'.
Run the query (click the toolbar button with the red [!] icon).

 

[John Spencer]

Where are you going to use this? If you are doing this for a report,
you might be able to do this in the report more easily than in a query.

Any query solution could be too slow unless your id's are sequential
with no gaps. If your table does conform to that requirement you can
use a totals query to get the sums.

SELECT (ID-1)\100 as TheGroupNumber
, SUM(Score) as TotalScore
FROM SomeTable
GROUP BY (ID-1)\100

You might want to group on
(((ID-1)\100) + 1) * 100 to get the numbers, 100, 200, 300, ...

Then you coulc use a union query

In a report, you would add a grouping level based on the equation
(ID-1)\100 and then add a control to the group footer with its source set to
= Sum(score)

If you don't have sequential ID numbers then the solution is a lot
tougher and can be a lot slower (perhaps too slow with 50,000 records).


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[pemt]

thanks a lot!

Where are you going to use this? If you are doing this for a report,
you might be able to do this in the report more easily than in a query.

Any query solution could be too slow unless your id's are sequential
with no gaps. If your table does conform to that requirement you can
use a totals query to get the sums.

SELECT (ID-1)\100 as TheGroupNumber
, SUM(Score) as TotalScore
FROM SomeTable
GROUP BY (ID-1)\100

You might want to group on
(((ID-1)\100) + 1) * 100 to get the numbers, 100, 200, 300, ...

Then you coulc use a union query

In a report, you would add a grouping level based on the equation
(ID-1)\100 and then add a control to the group footer with its source set to
= Sum(score)

If you don't have sequential ID numbers then the solution is a lot
tougher and can be a lot slower (perhaps too slow with 50,000 records).


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[pemt]

thanks a lot!

Pemt,

Make a query based on this table.

In the query design grid, in the Field row of the first column, enter like
this:
Hundreds: ([id]-1)\100+1
In the second column, put the score field.
Make this into a Totals query (select Totls from the View menu.
In the Totals row in the grid, leave it set to 'Group By' in the 1st column,
and in the 2nd column put 'Sum'.
Run the query (click the toolbar button with the red [!] icon).

--
Steve Schapel, Microsoft Access MVP

hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt