Is it possible to count number of specific text responses to item?

[bp_photog]

I'm using Access 2002 with Windows XP

If I have a field that can be filled with either of two words, e.g., Adult,
Youth, is there a way to count how many times the field shows one of the
words, e.g, "Adult"?

Or a variation on that, is it possible to count the Yes answers to a Yes/No
field?

Thanks,

 

[Rowan]

Assuming the field containing Adult or Youth is called Age then

SELECT Age, count(*) as Count
FROM Table1
Where Age = "Adult"
Group By Age

Take out the Where clause to get a count of Adult and Youth.

Regards
Rowan

 

[bp_photog]

Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

 

[Andreas]

- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas

 

[bp_photog]

Andreas

Thank you, thank you, thank you.

What you suggested worked perfectly. Thanks so much.

bp_photog

 

[bp_photog]

Andreas -

Thanks for your help. One more question: how can I write an expression in
the Report footer that will sum the count for Adults and another expression
that will sum the count for Youth? So I can get total Adults and total youth
across all the districts?

I know how to get a sum of the total AgeCount but can't figure how to
separate out the Adults and Youth.

Can this be done?

Thanks,

bp_photog

- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas

Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

:

 

[Andreas]

There are 2 solutions:

1)
Either create 2 seperate columns in the query.

2)
The easier way:
Have a look at the DCount() function.
Something like:
Dcount("[Age]","[Table1]","[Age] = 'Adult'")
Dcount("[Age]","[Table1]","[Age] = 'Youth'")

Regards,
Andreas

Andreas -

Thanks for your help. One more question: how can I write an expression in
the Report footer that will sum the count for Adults and another expression
that will sum the count for Youth? So I can get total Adults and total youth
across all the districts?

I know how to get a sum of the total AgeCount but can't figure how to
separate out the Adults and Youth.

Can this be done?

Thanks,

bp_photog

:

- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas

Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

:



Assuming the field containing Adult or Youth is called Age then

SELECT Age, count(*) as Count

FROM Table1

Where Age = "Adult"
Group By Age

Take out the Where clause to get a count of Adult and Youth.

Regards
Rowan

:



I'm using Access 2002 with Windows XP

If I have a field that can be filled with either of two words, e.g., Adult,
Youth, is there a way to count how many times the field shows one of the
words, e.g, "Adult"?

Or a variation on that, is it possible to count the Yes answers to a Yes/No
field?

Thanks,

 

[bp_photog]

Andreas:

I tried the Dcount experession and got a message saying I needed to add an
operator. I next tried it adding an "=" sign in front of Dcount and got a
message saying I had a syntax error. That I may have included an operator
without an operand.

Another question: should I include the name of the query in the

portion of the statement?

Thanks much for your help,

bp_photog

There are 2 solutions:

1)
Either create 2 seperate columns in the query.

2)
The easier way:
Have a look at the DCount() function.
Something like:
Dcount("[Age]","[Table1]","[Age] = 'Adult'")
Dcount("[Age]","[Table1]","[Age] = 'Youth'")

Regards,
Andreas

Andreas -

Thanks for your help. One more question: how can I write an expression in
the Report footer that will sum the count for Adults and another expression
that will sum the count for Youth? So I can get total Adults and total youth
across all the districts?

I know how to get a sum of the total AgeCount but can't figure how to
separate out the Adults and Youth.

Can this be done?

Thanks,

bp_photog

:

- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas


bp_photog wrote:

Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

:



Assuming the field containing Adult or Youth is called Age then

SELECT Age, count(*) as Count

FROM Table1

Where Age = "Adult"
Group By Age

Take out the Where clause to get a count of Adult and Youth.

Regards
Rowan

:



I'm using Access 2002 with Windows XP

If I have a field that can be filled with either of two words, e.g., Adult,
Youth, is there a way to count how many times the field shows one of the
words, e.g, "Adult"?

Or a variation on that, is it possible to count the Yes answers to a Yes/No
field?

Thanks,

 

[Andreas]

1)
In order to put a calculated expression into a textbox, you need to use
an "=". Sorry, I presumed you'd know that.
2)
You need to replace [Table1] with the name of the table or query where
the data comes from.

Regards,
Andreas

Andreas:

I tried the Dcount experession and got a message saying I needed to add an
operator. I next tried it adding an "=" sign in front of Dcount and got a
message saying I had a syntax error. That I may have included an operator
without an operand.

Another question: should I include the name of the query in the

portion of the statement?

Thanks much for your help,

bp_photog

:

There are 2 solutions:

1)
Either create 2 seperate columns in the query.

2)
The easier way:
Have a look at the DCount() function.
Something like:
Dcount("[Age]","[Table1]","[Age] = 'Adult'")
Dcount("[Age]","[Table1]","[Age] = 'Youth'")

Regards,
Andreas

Andreas -

Thanks for your help. One more question: how can I write an expression in
the Report footer that will sum the count for Adults and another expression
that will sum the count for Youth? So I can get total Adults and total youth
across all the districts?

I know how to get a sum of the total AgeCount but can't figure how to
separate out the Adults and Youth.

Can this be done?

Thanks,

bp_photog

:



- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas


bp_photog wrote:


Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

:




Assuming the field containing Adult or Youth is called Age then

SELECT Age, count(*) as Count

FROM Table1


Where Age = "Adult"
Group By Age

Take out the Where clause to get a count of Adult and Youth.

Regards
Rowan

:




I'm using Access 2002 with Windows XP

If I have a field that can be filled with either of two words, e.g., Adult,
Youth, is there a way to count how many times the field shows one of the
words, e.g, "Adult"?

Or a variation on that, is it possible to count the Yes answers to a Yes/No
field?

Thanks,

 

[bp_photog]

Actually I did use the "=" sign the first time I entered the statement. When
I received an error message saying I might have an operator without an
operand I tried the statement without the = sign. Then I received the
message saying I did not include an operator. I put it back in and got the
"operator without an operand" message again.

I used the name of the query the report was based upon in the

part
of the statement.

I'll run it again and check carefully to see if I miscopied the statement
somewhere.

Thanks again very much for helping me.

bp_photog

1)
In order to put a calculated expression into a textbox, you need to use
an "=". Sorry, I presumed you'd know that.
2)
You need to replace [Table1] with the name of the table or query where
the data comes from.

Regards,
Andreas

Andreas:

I tried the Dcount experession and got a message saying I needed to add an
operator. I next tried it adding an "=" sign in front of Dcount and got a
message saying I had a syntax error. That I may have included an operator
without an operand.

Another question: should I include the name of the query in the

portion of the statement?

Thanks much for your help,

bp_photog

:

There are 2 solutions:

1)
Either create 2 seperate columns in the query.

2)
The easier way:
Have a look at the DCount() function.
Something like:
Dcount("[Age]","[Table1]","[Age] = 'Adult'")
Dcount("[Age]","[Table1]","[Age] = 'Youth'")

Regards,
Andreas


bp_photog wrote:

Andreas -

Thanks for your help. One more question: how can I write an expression in
the Report footer that will sum the count for Adults and another expression
that will sum the count for Youth? So I can get total Adults and total youth
across all the districts?

I know how to get a sum of the total AgeCount but can't figure how to
separate out the Adults and Youth.

Can this be done?

Thanks,

bp_photog

:



- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas


bp_photog wrote:


Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

:




Assuming the field containing Adult or Youth is called Age then

SELECT Age, count(*) as Count

FROM Table1


Where Age = "Adult"
Group By Age

Take out the Where clause to get a count of Adult and Youth.

Regards
Rowan

:




I'm using Access 2002 with Windows XP

If I have a field that can be filled with either of two words, e.g., Adult,
Youth, is there a way to count how many times the field shows one of the
words, e.g, "Adult"?

Or a variation on that, is it possible to count the Yes answers to a Yes/No
field?

Thanks,

 

[Andreas]

Let's try this a different way:

Create a new query (give it a meaningful name but let's call it
qryAgeTotals for now):
SELECT [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY [Age]

In the 1st textbox in your report footer enter the expression:
=DLookup("AgeCount","qryAgeTotals","Age='Adult'")

In the 2nd textbox in your report footer enter the expression:
=DLookup("AgeCount","qryAgeTotals","Age='Youth'")

Regards,
Andreas

Actually I did use the "=" sign the first time I entered the statement. When
I received an error message saying I might have an operator without an
operand I tried the statement without the = sign. Then I received the
message saying I did not include an operator. I put it back in and got the
"operator without an operand" message again.

I used the name of the query the report was based upon in the

part
of the statement.

I'll run it again and check carefully to see if I miscopied the statement
somewhere.

Thanks again very much for helping me.

bp_photog

:

1)
In order to put a calculated expression into a textbox, you need to use
an "=". Sorry, I presumed you'd know that.
2)
You need to replace [Table1] with the name of the table or query where
the data comes from.

Regards,
Andreas

Andreas:

I tried the Dcount experession and got a message saying I needed to add an
operator. I next tried it adding an "=" sign in front of Dcount and got a
message saying I had a syntax error. That I may have included an operator
without an operand.

Another question: should I include the name of the query in the

portion of the statement?

Thanks much for your help,

bp_photog

:



There are 2 solutions:

1)
Either create 2 seperate columns in the query.

2)
The easier way:
Have a look at the DCount() function.
Something like:
Dcount("[Age]","[Table1]","[Age] = 'Adult'")
Dcount("[Age]","[Table1]","[Age] = 'Youth'")

Regards,
Andreas


bp_photog wrote:


Andreas -

Thanks for your help. One more question: how can I write an expression in
the Report footer that will sum the count for Adults and another expression
that will sum the count for Youth? So I can get total Adults and total youth
across all the districts?

I know how to get a sum of the total AgeCount but can't figure how to
separate out the Adults and Youth.

Can this be done?

Thanks,

bp_photog

:




- Go to the "Query" tab
- Click on "New"
- Click on "Design View"
- Click on "OK"
- When the "Show Table" dialog pops up, click "Cancel"
- Top left corner should be a button on the toolbar that says "SQL",
click on it.
- Delete all text in this window
- Paste the SQL statement as is
- Top left corner, same button (picture has changed), click on it
- You should now be in "Design View" where you can see what you are
asking for

SQL statement:
SELECT [District], [Age], Count(*) AS [AgeCount] FROM [Table1] GROUP BY
[District], [Age]

Just make sure you change the field names and table name within the
square brackets to the exact names you are using.
This will only work, if all the data is in a single table.

Regards,
Andreas


bp_photog wrote:



Rowan:

Thanks much. Hope you won't mind if I ask for more info.

I'm not sure how to do what you've indicated. In the design view, as I
understand Access I can put "Age" in as the Field, the next row shows the
Table, and then the Total row can show either "Group by" or "Count" or any of
the other calculation commands. Where should I put the (*)? And where
should I (can I) put the "Where Age = "Adult" line"?

Also, the other field I'm using is a geographical district that the
attendees come from. I'd like to group the data by districts with a count of
Adults and a count of Youth for each district.

Can I do this?

Thanks again,

bp_photog

:





Assuming the field containing Adult or Youth is called Age then

SELECT Age, count(*) as Count

FROM Table1



Where Age = "Adult"
Group By Age

Take out the Where clause to get a count of Adult and Youth.

Regards
Rowan

:





I'm using Access 2002 with Windows XP

If I have a field that can be filled with either of two words, e.g., Adult,
Youth, is there a way to count how many times the field shows one of the
words, e.g, "Adult"?

Or a variation on that, is it possible to count the Yes answers to a Yes/No
field?

Thanks,