Count

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

Guest

Hello,

I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result. Thank you in advance.


John
 
John said:
Hello,

I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result. In the datasheet I would like to see the total number of males, not just 1's. Thank you in advance.


John
Click to expand...
 
John said:
Hello,

I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result. Thank you in advance.


John
Click to expand...
Hi John,

First, you shouldn't use "count" as the name for your calculated field.
COUNT() is a reserved word in Access, meaning it refers to a specific
function that performs a specific action. Access can get really confused
if you use reserved words as table or field names.

Second, I don't understand why you're including the Abs() in your
function...do you really expect to get a negative value for either
gender ("CountOfSexes = -29")? Also, what datatype is the information in
the Gender field? If, as I suspect, you just have 2 possible text
entries - M or F - then you can't Sum() this field. Try

SELECT Gender, Count([Gender]) AS CountOfSexes
FROM MyTable
GROUP BY Gender;

This will give you a count of the number of records for each gender group.

hth,

LeAnne
 
Hi,

My entries for the Gender is Male or Female. And where and how should I
apply you suggestion: SELECT Gender, Count([Gender]) AS CountOfSexes
FROM MyTable
GROUP BY Gender;
Click to expand...

Thank you

John


LeAnne said:
John said:
Hello,

I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result. Thank you in advance.


John
Click to expand...
Hi John,

First, you shouldn't use "count" as the name for your calculated field.
COUNT() is a reserved word in Access, meaning it refers to a specific
function that performs a specific action. Access can get really confused
if you use reserved words as table or field names.

Second, I don't understand why you're including the Abs() in your
function...do you really expect to get a negative value for either
gender ("CountOfSexes = -29")? Also, what datatype is the information in
the Gender field? If, as I suspect, you just have 2 possible text
entries - M or F - then you can't Sum() this field. Try

SELECT Gender, Count([Gender]) AS CountOfSexes
FROM MyTable
GROUP BY Gender;

This will give you a count of the number of records for each gender group.

hth,

LeAnne
Click to expand...
 
John said:
I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result.
Click to expand...


If you don't care about the education field, you can use
something like this:

SELECT Abs(Sum(Gender = "Male")) As Males,
Abs(Sum(Gender = "Female")) As Females
FROM thetable
 
Hi,

My entries for the Gender is Male or Female. And where and how should I
apply you suggestion: SELECT Gender, Count([Gender]) AS CountOfSexes
FROM MyTable
GROUP BY Gender;
Click to expand...
Click to expand...

Create a new Query. Open it in SQL view (using the View menu item).
Copy and paste this SQL into the query.

Or, create a new query based on your table. Add the Gender field to
the query grid *twice*. Change the query to a Totals query using the
Greek Sigma icon (looks like a sideways W) and use "Group By" on the
Totals line under one of the Gender fields, and Count under the other.
Open the query by clicking the leftmost toolbar icon and you'll get
your counts.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Marshall,

First, Where do I type :SELECT Abs(Sum(Gender = "Male")) As Males,
Abs(Sum(Gender = "Female")) As Females
Click to expand...
I tried typing it in a new grid next to the Gender in the expression builder
and when I try to run the query I get a message "You tried to execute a query
that does not include the specified expression aggregate function ....". I
think I would like someone to tell me in step by step how to input the above
expression. Thank you.

John


Marshall Barton said:
John said:
I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result.
Click to expand...


If you don't care about the education field, you can use
something like this:

SELECT Abs(Sum(Gender = "Male")) As Males,
Abs(Sum(Gender = "Female")) As Females
FROM thetable
Click to expand...
 
Hi,

Now that you got the idea, here is what I need to complete in this task. I
would like to include the two fields from my table: Gender and Education into
the query. I would like to create a parameter in the criteria for the
education field to request which the users input and at the same time I would
like the Gender field to count the number of males and females in the query.
Basically, when I press the run ! it should display me the requested
education and the count of males or females. I don't mind creating another
query for the two genders to separate them. I just want to know at least for
one of the genders for now then or if possible both. I appreciate your help.


John
 
You have to use SQL view when you type an SQL statement.

If you can read SQL, you could translate it into the query
design grid by just entering the two fields:

Males: Abs(Sum(Gender = "Male"))
Females: Abs(Sum(Gender = "Female"))
--
Marsh
MVP [MS Access]


First, Where do I type :SELECT Abs(Sum(Gender = "Male")) As Males,
Abs(Sum(Gender = "Female")) As Females
Click to expand...
I tried typing it in a new grid next to the Gender in the expression builder
and when I try to run the query I get a message "You tried to execute a query
that does not include the specified expression aggregate function ....". I
think I would like someone to tell me in step by step how to input the above
expression.
I have a table with 2 fields. First field name is Gender, and the second is
education.
From the query in design view, I would like to count the number of males and
females from the Gender field. I don't mind using another grid for the
female. I need to know what expression I need to use and where to use the
expression. I appreciate if you can show me step by step how to do this. I
tried using Count:Abs(Sum([gender])), but no result.
Click to expand...
Click to expand...
Marshall Barton said:
If you don't care about the education field, you can use
something like this:

SELECT Abs(Sum(Gender = "Male")) As Males,
Abs(Sum(Gender = "Female")) As Females
FROM thetable
Click to expand...
Click to expand...
 
Now that you got the idea, here is what I need to complete in this task. I
would like to include the two fields from my table: Gender and Education into
the query. I would like to create a parameter in the criteria for the
education field to request which the users input and at the same time I would
like the Gender field to count the number of males and females in the query.
Basically, when I press the run ! it should display me the requested
education and the count of males or females. I don't mind creating another
query for the two genders to separate them. I just want to know at least for
one of the genders for now then or if possible both. I appreciate your help.
Click to expand...

To reitierate, with some expansion:

Create a new query based on your table. Add the Gender field to
the query grid *twice*. Change the query to a Totals query using the
Greek Sigma icon (looks like a sideways W) and use "Group By" on the
Totals line under one of the Gender fields, and Count under the other.
Add the Education field; change the Totals row under it to "Where". On
the Criteria line under the Education field put

[Enter education level:]

Open the query by clicking the leftmost toolbar icon and you'll get
your counts, after you respond to the prompt Enter education level:.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

I tried what you mentioned and it works great, but with one condition. That
is to uncheck the box for the education grid in the query design. Now, that
leaves me with two more questions.
1. Why unchecking only works?
2. Why when I try adding the two gender fields and then try to add a field
with a combo box option created in the table, I cannot use my parameter
option? Meaning the query runs but does not display any records. My
parameter works only for the fields with regular text datatype but not with
fields with combo or dropdown list. Thank you for your help.

John

John Vinson said:
Now that you got the idea, here is what I need to complete in this task. I
would like to include the two fields from my table: Gender and Education into
the query. I would like to create a parameter in the criteria for the
education field to request which the users input and at the same time I would
like the Gender field to count the number of males and females in the query.
Basically, when I press the run ! it should display me the requested
education and the count of males or females. I don't mind creating another
query for the two genders to separate them. I just want to know at least for
one of the genders for now then or if possible both. I appreciate your help.
Click to expand...

To reitierate, with some expansion:

Create a new query based on your table. Add the Gender field to
the query grid *twice*. Change the query to a Totals query using the
Greek Sigma icon (looks like a sideways W) and use "Group By" on the
Totals line under one of the Gender fields, and Count under the other.
Add the Education field; change the Totals row under it to "Where". On
the Criteria line under the Education field put

[Enter education level:]

Open the query by clicking the leftmost toolbar icon and you'll get
your counts, after you respond to the prompt Enter education level:.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
John,

I tried what you mentioned and it works great, but with one condition. That
is to uncheck the box for the education grid in the query design. Now, that
leaves me with two more questions.
1. Why unchecking only works?
Click to expand...

A WHERE operator on the Totals row means that the field should only be
used for selecting records, not for display. If you know that every
record will have the Education checkbox checked, why do you need to
see it? Could you explain "the education grid"? How is Education
stored in your table? I was assuming a single Text field.
2. Why when I try adding the two gender fields and then try to add a field
with a combo box option created in the table, I cannot use my parameter
option? Meaning the query runs but does not display any records. My
parameter works only for the fields with regular text datatype but not with
fields with combo or dropdown list. Thank you for your help.
Click to expand...

You have not mentioned "two gender fields", and I really have NO idea
what you mean. Again... my assumption was that you had a single text
field named [Gender] in your table, with values "M" and "F" (or,
depending on the context, "M", "GM", "F", "LF", "MtFTG", ...). Now
you're saying "adding the two gender fields".

Please post a description of your table structure. Note the datatype
of each relevant field, and whether you've fallen victim to the
abominable "Lookup Wizard" misfeature. I'm clearly answering the wrong
questions because I do not understand your table structure!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

My [Gender] field is text datatype with a lookup wizard -combo box."Male" or
"Female"
My [Education] field is text datatype with a lookup wizard - combo box. And
the the options are "high school" "some college" "no college". Now, I want
to be able to use a query to find how many of the students are female with
"no college". How many of the students are male with "high school" education.
I want to be flexible in searching for the count of males and females.
Thank you so much.

John


A WHERE operator on the Totals row means that the field should only be
used for selecting records, not for display. If you know that every
record will have the Education checkbox checked, why do you need to
see it? Could you explain "the education grid"? How is Education
stored in your table? I was assuming a single Text field.
2. Why when I try adding the two gender fields and then try to add a field
with a combo box option created in the table, I cannot use my parameter
option? Meaning the query runs but does not display any records. My
parameter works only for the fields with regular text datatype but not with
fields with combo or dropdown list. Thank you for your help.
Click to expand...

You have not mentioned "two gender fields", and I really have NO idea
what you mean. Again... my assumption was that you had a single text
field named [Gender] in your table, with values "M" and "F" (or,
depending on the context, "M", "GM", "F", "LF", "MtFTG", ...). Now
you're saying "adding the two gender fields".

Please post a description of your table structure. Note the datatype
of each relevant field, and whether you've fallen victim to the
abominable "Lookup Wizard" misfeature. I'm clearly answering the wrong
questions because I do not understand your table structure!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
John,

My [Gender] field is text datatype with a lookup wizard -combo box."Male" or
"Female"
My [Education] field is text datatype with a lookup wizard - combo box. And
the the options are "high school" "some college" "no college".
Click to expand...

Ok. You have fallen victim to one of Microsoft's most abominable
misfeatures.

It's very likely that your table APPEARS to contain the text "high
school", "some college", "no college" and "Male" or "Female".

It's even more likely that it doesn't contain this text. It contains
numbers in those fields. These numbers are put there by the Lookup
Wizard, which then conceals them behind the Lookup field.

Try changing the Lookup property of these fields from Combo to Textbox
(don't save the table, yet) to see if that is in fact the case.
Now, I want
to be able to use a query to find how many of the students are female with
"no college". How many of the students are male with "high school" education.
I want to be flexible in searching for the count of males and females.
Thank you so much.
Click to expand...

Fine... but you didn't ask that question previously. You said you
wanted a count. The Totals query that I and others suggested would
give you that count, for all combinations of education and gender.

Please do the check I suggest on the fields; I'll be curious to see if
Access is actually storing the text or if it has created a numeric ID.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top