Grouping and counting instances

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query:

SELECT CityID, Age, Sex
FROM PDL
GROUP BY CityID, Age, Sex
ORDER By CityID

This returns all of the information that I need. However, I would like to do
some counts and grouping on this data. What is the best way for me to get a
breakdown of the number of people by Age and Sex that belong to each CityID?
There are multiple occurences of CityID in the table (77 different CityIDs).
 
I have figured out how to accomplish this with the following query:

SELECT Table.CityID, Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female, Sum(IIf([Age]="18-20",1,0)) AS
["18-20"], Sum(IIf([Age]="21-24",1,0)) AS ["21-24"],
Sum(IIf([Age]="25-29",1,0)) AS ["25-29"], Sum(IIf([Age]="30-34",1,0)) AS
["30-34"], Sum(IIf([Age]="35-39",1,0)) AS ["35-39"],
Sum(IIf([Age]="40-44",1,0)) AS ["40-44"], Sum(IIf([Age]="45-49",1,0)) AS
["45-49"], Sum(IIf([Age]="50-54",1,0)) AS ["50-54"],
Sum(IIf([Age]="55-59",1,0)) AS ["55-59"], Sum(IIf([Age]="60-69",1,0)) AS
["60-69"], Sum(IIf([Age]="70 or older",1,0)) AS ["70+"]

FROM PDLTable

GROUP BY Table.CityID, PDLTable.Sex;

I did it this way becuase the Age data was stored in the form of ranges
instead of a single number.

However, how would I go about accomplishing this type of breakdown if the
ages were stated as a single digit instead of range? Ideally, I would like to
see the single digit age assigned to an age range so that once I run the
query the the data would be grouped accordingly.

Example:

CityID =2 Male = 3 Age = 20
CityID =2 Female= 5 Age= 18

I would like for the the resulting query to look like
Age Range 18-20
CityID 2 Male 3 1
Female 5 1
 
SELECT Table.CityID
, Sum(IIf([Sex]="Male",1,0)) AS Male
, Sum(IIf([Sex]="Female",1,0)) AS Female
, Sum(IIf([Age Between 18 and 20,1,0)) AS [18-20]
, Sum(IIf([Age] Between 21 and 24,1,0)) AS [21-24],
....
, Sum(IIf([Age] >=70 ,1,0)) AS [70+]
FROM PDLTable
GROUP BY ...


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rubydee said:
I have figured out how to accomplish this with the following query:

SELECT Table.CityID, Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female, Sum(IIf([Age]="18-20",1,0)) AS
["18-20"], Sum(IIf([Age]="21-24",1,0)) AS ["21-24"],
Sum(IIf([Age]="25-29",1,0)) AS ["25-29"], Sum(IIf([Age]="30-34",1,0)) AS
["30-34"], Sum(IIf([Age]="35-39",1,0)) AS ["35-39"],
Sum(IIf([Age]="40-44",1,0)) AS ["40-44"], Sum(IIf([Age]="45-49",1,0)) AS
["45-49"], Sum(IIf([Age]="50-54",1,0)) AS ["50-54"],
Sum(IIf([Age]="55-59",1,0)) AS ["55-59"], Sum(IIf([Age]="60-69",1,0)) AS
["60-69"], Sum(IIf([Age]="70 or older",1,0)) AS ["70+"]

FROM PDLTable

GROUP BY Table.CityID, PDLTable.Sex;

I did it this way becuase the Age data was stored in the form of ranges
instead of a single number.

However, how would I go about accomplishing this type of breakdown if the
ages were stated as a single digit instead of range? Ideally, I would like
to
see the single digit age assigned to an age range so that once I run the
query the the data would be grouped accordingly.

Example:

CityID =2 Male = 3 Age = 20
CityID =2 Female= 5 Age= 18

I would like for the the resulting query to look like
Age Range 18-20
CityID 2 Male 3 1
Female 5 1


Rubydee said:
I have the following query:

SELECT CityID, Age, Sex
FROM PDL
GROUP BY CityID, Age, Sex
ORDER By CityID

This returns all of the information that I need. However, I would like to
do
some counts and grouping on this data. What is the best way for me to get
a
breakdown of the number of people by Age and Sex that belong to each
CityID?
There are multiple occurences of CityID in the table (77 different
CityIDs).
Click to expand...
Click to expand...
 
Thanks, John. I tried what you suggested, but I am getting an error. Here is
the SQL and the error message:

SELECT CityID,
Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female,
Sum(IIf([Age] Between 018 and 020,1,0)) AS [18-20],
Sum(IIf([Age] Between 021 and 024,1,0)) AS [21-24],
Sum(IIf([Age] Between 025 and 029,1,0)) AS [25-29],
......
Sum(IIf([Age] >=070 ,1,0)) AS [70+]

FROM Table
GROUP BY CityID, Sex
WHERE (((CityID) Like "371*"));

ERROR MESSAGE: Syntax error (missing operator) in query expression 'Sex
WHERE (((CityID) Like "371"))'.



John Spencer said:
SELECT Table.CityID
, Sum(IIf([Sex]="Male",1,0)) AS Male
, Sum(IIf([Sex]="Female",1,0)) AS Female
, Sum(IIf([Age Between 18 and 20,1,0)) AS [18-20]
, Sum(IIf([Age] Between 21 and 24,1,0)) AS [21-24],
....
, Sum(IIf([Age] >=70 ,1,0)) AS [70+]
FROM PDLTable
GROUP BY ...


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rubydee said:
I have figured out how to accomplish this with the following query:

SELECT Table.CityID, Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female, Sum(IIf([Age]="18-20",1,0)) AS
["18-20"], Sum(IIf([Age]="21-24",1,0)) AS ["21-24"],
Sum(IIf([Age]="25-29",1,0)) AS ["25-29"], Sum(IIf([Age]="30-34",1,0)) AS
["30-34"], Sum(IIf([Age]="35-39",1,0)) AS ["35-39"],
Sum(IIf([Age]="40-44",1,0)) AS ["40-44"], Sum(IIf([Age]="45-49",1,0)) AS
["45-49"], Sum(IIf([Age]="50-54",1,0)) AS ["50-54"],
Sum(IIf([Age]="55-59",1,0)) AS ["55-59"], Sum(IIf([Age]="60-69",1,0)) AS
["60-69"], Sum(IIf([Age]="70 or older",1,0)) AS ["70+"]

FROM PDLTable

GROUP BY Table.CityID, PDLTable.Sex;

I did it this way becuase the Age data was stored in the form of ranges
instead of a single number.

However, how would I go about accomplishing this type of breakdown if the
ages were stated as a single digit instead of range? Ideally, I would like
to
see the single digit age assigned to an age range so that once I run the
query the the data would be grouped accordingly.

Example:

CityID =2 Male = 3 Age = 20
CityID =2 Female= 5 Age= 18

I would like for the the resulting query to look like
Age Range 18-20
CityID 2 Male 3 1
Female 5 1


Rubydee said:
I have the following query:

SELECT CityID, Age, Sex
FROM PDL
GROUP BY CityID, Age, Sex
ORDER By CityID

This returns all of the information that I need. However, I would like to
do
some counts and grouping on this data. What is the best way for me to get
a
breakdown of the number of people by Age and Sex that belong to each
CityID?
There are multiple occurences of CityID in the table (77 different
CityIDs).
Click to expand...
Click to expand...
Click to expand...
 
You have the WHERE clause in wrong place

SELECT CityID,
Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female,
Sum(IIf([Age] Between 018 and 020,1,0)) AS [18-20],
Sum(IIf([Age] Between 021 and 024,1,0)) AS [21-24],
Sum(IIf([Age] Between 025 and 029,1,0)) AS [25-29],
......
Sum(IIf([Age] >=070 ,1,0)) AS [70+]

FROM Table
WHERE CityID Like "371*"
GROUP BY CityID, Sex

You could use a HAVING clause instead, but that should really only be
used when you are using one of the aggregate operation such as Sum, Avg,
etc.


SELECT CityID,
Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female,
Sum(IIf([Age] Between 018 and 020,1,0)) AS [18-20],
Sum(IIf([Age] Between 021 and 024,1,0)) AS [21-24],
Sum(IIf([Age] Between 025 and 029,1,0)) AS [25-29],
......
Sum(IIf([Age] >=070 ,1,0)) AS [70+]
FROM Table
GROUP BY CityID, Sex
HAVING CityID Like "371*"

The first query will run faster since the WHERE clause will execute
before all the grouping and summing takes place, so only those records
that meet the criteria are grouped and summed.

The Having clause applies criteria AFTER ALL the records in the table
are grouped and summed.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks, John. I tried what you suggested, but I am getting an error. Here is
the SQL and the error message:

SELECT CityID,
Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female,
Sum(IIf([Age] Between 018 and 020,1,0)) AS [18-20],
Sum(IIf([Age] Between 021 and 024,1,0)) AS [21-24],
Sum(IIf([Age] Between 025 and 029,1,0)) AS [25-29],
.....
Sum(IIf([Age] >=070 ,1,0)) AS [70+]

FROM Table
GROUP BY CityID, Sex
WHERE (((CityID) Like "371*"));

ERROR MESSAGE: Syntax error (missing operator) in query expression 'Sex
WHERE (((CityID) Like "371"))'.



John Spencer said:
SELECT Table.CityID
, Sum(IIf([Sex]="Male",1,0)) AS Male
, Sum(IIf([Sex]="Female",1,0)) AS Female
, Sum(IIf([Age Between 18 and 20,1,0)) AS [18-20]
, Sum(IIf([Age] Between 21 and 24,1,0)) AS [21-24],
....
, Sum(IIf([Age] >=70 ,1,0)) AS [70+]
FROM PDLTable
GROUP BY ...


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rubydee said:
I have figured out how to accomplish this with the following query:

SELECT Table.CityID, Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female, Sum(IIf([Age]="18-20",1,0)) AS
["18-20"], Sum(IIf([Age]="21-24",1,0)) AS ["21-24"],
Sum(IIf([Age]="25-29",1,0)) AS ["25-29"], Sum(IIf([Age]="30-34",1,0)) AS
["30-34"], Sum(IIf([Age]="35-39",1,0)) AS ["35-39"],
Sum(IIf([Age]="40-44",1,0)) AS ["40-44"], Sum(IIf([Age]="45-49",1,0)) AS
["45-49"], Sum(IIf([Age]="50-54",1,0)) AS ["50-54"],
Sum(IIf([Age]="55-59",1,0)) AS ["55-59"], Sum(IIf([Age]="60-69",1,0)) AS
["60-69"], Sum(IIf([Age]="70 or older",1,0)) AS ["70+"]

FROM PDLTable

GROUP BY Table.CityID, PDLTable.Sex;

I did it this way becuase the Age data was stored in the form of ranges
instead of a single number.

However, how would I go about accomplishing this type of breakdown if the
ages were stated as a single digit instead of range? Ideally, I would like
to
see the single digit age assigned to an age range so that once I run the
query the the data would be grouped accordingly.

Example:

CityID =2 Male = 3 Age = 20
CityID =2 Female= 5 Age= 18

I would like for the the resulting query to look like
Age Range 18-20
CityID 2 Male 3 1
Female 5 1


:

I have the following query:

SELECT CityID, Age, Sex
FROM PDL
GROUP BY CityID, Age, Sex
ORDER By CityID

This returns all of the information that I need. However, I would like to
do
some counts and grouping on this data. What is the best way for me to get
a
breakdown of the number of people by Age and Sex that belong to each
CityID?
There are multiple occurences of CityID in the table (77 different
CityIDs).
Click to expand...
Click to expand...
Click to expand...
 
rubydee73 said:
Thanks, John. I tried what you suggested, but I am getting an error. Here is
the SQL and the error message:

SELECT CityID,
Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female,
Sum(IIf([Age] Between 018 and 020,1,0)) AS [18-20],
Sum(IIf([Age] Between 021 and 024,1,0)) AS [21-24],
Sum(IIf([Age] Between 025 and 029,1,0)) AS [25-29],
.....
Sum(IIf([Age] >=070 ,1,0)) AS [70+]

FROM Table
GROUP BY CityID, Sex
WHERE (((CityID) Like "371*"));

ERROR MESSAGE: Syntax error (missing operator) in query expression 'Sex
WHERE (((CityID) Like "371"))'.



John Spencer said:
SELECT Table.CityID
, Sum(IIf([Sex]="Male",1,0)) AS Male
, Sum(IIf([Sex]="Female",1,0)) AS Female
, Sum(IIf([Age Between 18 and 20,1,0)) AS [18-20]
, Sum(IIf([Age] Between 21 and 24,1,0)) AS [21-24],
....
, Sum(IIf([Age] >=70 ,1,0)) AS [70+]
FROM PDLTable
GROUP BY ...


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rubydee said:
I have figured out how to accomplish this with the following query:

SELECT Table.CityID, Sum(IIf([Sex]="Male",1,0)) AS Male,
Sum(IIf([Sex]="Female",1,0)) AS Female, Sum(IIf([Age]="18-20",1,0)) AS
["18-20"], Sum(IIf([Age]="21-24",1,0)) AS ["21-24"],
Sum(IIf([Age]="25-29",1,0)) AS ["25-29"], Sum(IIf([Age]="30-34",1,0)) AS
["30-34"], Sum(IIf([Age]="35-39",1,0)) AS ["35-39"],
Click to expand...
Click to expand...
Click to expand...


Maybe more parentheses around the criteria.
NOt sure that it will help, but I seem to remember it working for me once.


SELECT CityID,
Sum(IIf(([Sex]="Male"),1,0)) AS Male,
Sum(IIf(([Sex]="Female"),1,0)) AS Female,
Sum(IIf(([Age] Between 018 and 020),1,0)) AS [18-20],
Sum(IIf(([Age] Between 021 and 024),1,0)) AS [21-24],
Sum(IIf(([Age] Between 025 and 029),1,0)) AS [25-29],
......
Sum(IIf(([Age] >=070),1,0)) AS [70+]

FROM Table
GROUP BY CityID, Sex
WHERE (((CityID) Like "371*"));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why can't I change data programmatically? 1
Query 11
Form help 4
Colors for Field/ 4
Null values and combo boxes--a problem 1
Combo boxes producing error message 3
3NF 6
Report by group in columns 8

Back
Top