Query that will aggregate a group

[Jennifer]

I have a table of populations by single age and gender

GENDER AGE POPULATION
Male 0 3000
Male 1 3050
Male 2 3005
Male 3 3050
Male 4 3010
Male 5 2950
Male 6 3000
Male 7 3500
Male 8 3550
Male 9 3050

I would like a table that aggregates by age grouping:

GENDER AGEGR POPULATION
Male 00-04 15115
Male 05-09 16050
Male 10-14 16700
Male 15-19 15925
etc....

Thank you for any assistance you can provide.

Jennifer

 

[Al Campagna]

Jennifer,
Create a table (tblAgeGroups) like this...
Age AgeGroup
0 1
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 2
9 2
10 3 etc....

Relate this table to your data table... Age to Age... and group the report
on
AgeGroup
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[James A. Fortune]

I have a table of populations by single age and gender

GENDER AGE POPULATION
Male 0 3000
Male 1 3050
Male 2 3005
Male 3 3050
Male 4 3010
Male 5 2950
Male 6 3000
Male 7 3500
Male 8 3550
Male 9 3050

I would like a table that aggregates by age grouping:

GENDER AGEGR POPULATION
Male 00-04 15115
Male 05-09 16050
Male 10-14 16700
Male 15-19 15925
etc....

Thank you for any assistance you can provide.

Jennifer

The expression AGE \ 5 will produce a number you can use to aggregate
your sum for a particular gender. It can also be used to create your
AGEGR strings.

James A. Fortune
(e-mail address removed)

 

[Jerry Whittle]

SELECT DISTINCTROW GENDERAGEPOPULATION.GENDER,
Int(([AGE])/5)*5 & " - " & Int((([AGE])/5)+1)*5 AS AGEGR,
Sum(GENDERAGEPOPULATION.POPULATION) AS ThePOPULATION
FROM GENDERAGEPOPULATION
GROUP BY GENDERAGEPOPULATION.GENDER,
Int(([AGE])/5)*5 & " - " & Int((([AGE])/5)+1)*5;

Make sure to change the above with the proper table and field names.

 

[James A. Fortune]

The expression AGE \ 5 will produce a number you can use to aggregate
your sum for a particular gender. It can also be used to create your
AGEGR strings.

James A. Fortune
(e-mail address removed)

How about:

SELECT GENDER, Format((AGE\5) * 5,'00') & "-" & Format((AGE\5) *
5+4,'00') AS AGEGP, Sum(tblPopulations.Population) As POPULATION
FROM tblPopulations GROUP BY GENDER, (AGE \ 5) * 5;

That produced:

GENDER AGEGP POPULATION
Male 00-04 15115
Male 05-09 16050

Expressions derived from a GROUP BY expression are sometimes close
enough to keep from getting a "You tried to execute a query that ...
as part of an aggregate function" error.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Expressions derived from a GROUP BY expression are sometimes close
enough to keep from getting a "You tried to execute a query that ...
as part of an aggregate function" error.

I discovered by trial and error that the final '* 5' is not required
at the end of the GROUP BY either, but the parentheses seem to be
required when grouping with an expression containing the '\' operator,
but I didn't check the operator hierachy to see if the parentheses are
required for that reason. I.e.,

SELECT GENDER, Format((AGE\5) * 5,'00') & "-" & Format((AGE\5) *
5+4,'00') AS AGEGP, Sum(tblPopulations.Population) As POPULATION
FROM tblPopulations GROUP BY GENDER, (AGE \ 5) * 5;

Apparently, simple multiplication by a constant, addition and
formatting are considered to be close enough to avoid the aggregate
error. Maybe more exciting possibilities will be discovered with
further testing. Most functions recognized by SQL in Access allow
expressions containing fields to be used as arguments. I expect those
functions to behave nicely like the Format() function in avoiding the
aggregate function error.

James A. Fortune
(e-mail address removed)