Using Count in a query

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

Guest

Hi. Below is a table with replies in "0" AND "1" How do I create a query to
count the number of "0" and "1" in this column?

This is your first visit
1
1
0
1
1
1
1
1
0
1
0
The "0" represents "No" and "1" represents "Yes".
Please Help!!!!!
 
SELECT Sum(myfield) As CountOfOnes,
Count(*)-Sum(myfield) As CountOfZeros
FROM MyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
SELECT MyField, Count(MyField) AS CountOfMyField
FROM MyTable
GROUP BY MyField

Substitute the actual field and table names, and paste the expression in
the SQL view of a new query. To get there, start making a new query in
design view, add no table and revert to SQL view.

HTH,
Nikos
 
Create a new query, click on the SQL view and paste it in there.
The star means "any field", and as you are just doing a count of records it
makes no difference what field you specifiy, so "*" is used.
 
1. Create a new query in design view.
2. Close the Show Table dialog.
3. Select SQL from the View toolbar (which is just under the File menu).
4. Replace what's there with the SQL statement that Nikos or I gave you.
5. Rename "myfield" to match the name of the field you have.
6. Rename MyTable to match the name of the table you have.
7. Select Save from the File menu.
8. Give the query a name, and click OK.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Hi,
I tried what you suggeted and when I went to name the query and save I get
an err0r saying" Syntax Error( Missing operator) in 'Sum(This is your first
visit) As CountOfOnes'
Below are the statements I entered.
SELECT Sum(This is your first visit) As CountOfOnes,
Count(*)-Sum(This is your first visit) As CountOfZeros
FROM Patient Survey

Thanka for all your help.
 
If any object name has spaces in it, it has to be enclosed in square
brackets:
SELECT Sum([This is your first visit]) As CountOfOnes,
Count(*)-Sum([This is your first visit]) As CountOfZeros
FROM [Patient Survey]

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Thanks a lot u were a great help!!!!!!!1

Graham R Seach said:
If any object name has spaces in it, it has to be enclosed in square
brackets:
SELECT Sum([This is your first visit]) As CountOfOnes,
Count(*)-Sum([This is your first visit]) As CountOfZeros
FROM [Patient Survey]

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

FC said:
Hi,
I tried what you suggeted and when I went to name the query and save I get
an err0r saying" Syntax Error( Missing operator) in 'Sum(This is your
first
visit) As CountOfOnes'
Below are the statements I entered.
SELECT Sum(This is your first visit) As CountOfOnes,
Count(*)-Sum(This is your first visit) As CountOfZeros
FROM Patient Survey

Thanka for all your help.
Click to expand...
Click to expand...
 
Back
Top