Help with SQL statement

[Ben]

I need to make a report for my website that lists these things

Base,
Total Number of Participants for that base,
Total number of Male Participants for that base,
Total number of female Participants for that base,
and the ages of the users (9-18).

EX:
-----------------------------------------------
Base |Total | Male | Female | Age/Number |
-----------------------------------------------
Cleveland | 20 | 10 | 10 | 9/1 |
| | | |10/2 |
-----------------------------------------------

So far my SQL statement looks like this:
strSQL = "SELECT base, count(gender) as totalMale WHERE gender = male,
count(gender) as totalFemale WHERE gender = female, count(base) as
totalParticipants FROM tblUsers WHERE admin = 0"

As you can probably tell it's not working, I don't think that this is
the right way to get the information that I am needing. Is there a different
way to get this information? Thanks!

 

[Bob Barrows [MVP]]

Database type and version? this is always relevant.

Table structure of tblUsers?

As a guess, i think you need a crosstab, but it's hard to be sure without
details.

A few rows of sample data (relevant columns only) would help.

Bob Barrows

 

[Ben]

Database Type and Version
Im Using Access 2000.

A few rows of relevant data inside of tblUsers

---------------------------
ID |Gender |Base |Age |
---------------------------

ID: autonumber (int)
Gender: Male or Female (text)
Base: list of the 13 bases (text)
Age: Number between 9 and 18 (int)

-=Ben
-=To email me take out the joke.

 

[Bob Barrows [MVP]]

Im Using Access 2000.

---------------------------
ID |Gender |Base |Age |
---------------------------

ID: autonumber (int)
Gender: Male or Female (text)
Base: list of the 13 bases (text)
Age: Number between 9 and 18 (int)

Where are the rows of data?
Never mind, I think (hope) I have the idea.

You will need to combine the results of two queries:
To get the gender portion broken out, use the Access Crosstab Query Wizard
to generate the sql for you..

The age breakdown can be done by a grouping query.

Then join these two queries to get the final result.

Play around with it for awhile. If you get stuck, get back to us.

Bob Barrows