Need a query to count 'Yes' values per month

G

Guest

Hi

How o I make a query so that I can count the number of "yes" responses and
have the query grouped per month - basically want to count the number of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
 
G

Guest

Assuming that the DATE field is date/time data type and Response is Yes/No
data type:

SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Sum(Abs([Response])) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
ORDER BY Year([DATE]), Month([DATE]);

If Response is not a Yes/No field:
SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Count(TblBrian.Response) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
HAVING (((Count(TblBrian.Response))<>No))
ORDER BY Year([DATE]), Month([DATE]);

BTW: DATE is a bad name for a field. It's a reserved word in Access. If you
don't put the [] around it, you could get some strange results. ResponseDate
would be better, but you would need to modify all queries, forms, and reports
that use the DATE field.
 
G

Guest

I think that HAVING (((Count(TblBrian.Response))<>No))
should read
HAVING TblBrian.Response<>"No"

--
KARL DEWEY
Build a little - Test a little


Jerry Whittle said:
Assuming that the DATE field is date/time data type and Response is Yes/No
data type:

SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Sum(Abs([Response])) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
ORDER BY Year([DATE]), Month([DATE]);

If Response is not a Yes/No field:
SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Count(TblBrian.Response) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
HAVING (((Count(TblBrian.Response))<>No))
ORDER BY Year([DATE]), Month([DATE]);

BTW: DATE is a bad name for a field. It's a reserved word in Access. If you
don't put the [] around it, you could get some strange results. ResponseDate
would be better, but you would need to modify all queries, forms, and reports
that use the DATE field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Brian said:
Hi

How o I make a query so that I can count the number of "yes" responses and
have the query grouped per month - basically want to count the number of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
Click to expand...
Click to expand...
 
J

John Spencer

In the query grid
Add your date field and response field and response field a second time
Select View Total from the menu
Change GROUP BY under ONE response field to Count
Change the Date field to Format([Date],"yyyy-mm")
Run the query. It will show the number of yeses and the number of noes for
each month

If you aren't concerned about the year than change the format to "mm" (for
2-digit months) or "mmm" for abbreviated months or "mmmm" for full month
names.

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

Guest

Hi, if I try this

SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Count(TblBrian.Response) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
HAVING (((Count(TblBrian.Response))<>No))
ORDER BY Year([DATE]), Month([DATE]);

it counts both the Yes and No for each month

if I try this one

SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Sum(Abs([Response])) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
ORDER BY Year([DATE]), Month([DATE]);

I get data mismatch in in criteria expression

Thanks for looking

Jerry Whittle said:
Assuming that the DATE field is date/time data type and Response is Yes/No
data type:

SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Sum(Abs([Response])) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
ORDER BY Year([DATE]), Month([DATE]);

If Response is not a Yes/No field:
SELECT Year([DATE]) AS TheYear,
Month([DATE]) AS TheMonth,
Count(TblBrian.Response) AS YesResponses
FROM TblBrian
GROUP BY Year([DATE]), Month([DATE])
HAVING (((Count(TblBrian.Response))<>No))
ORDER BY Year([DATE]), Month([DATE]);

BTW: DATE is a bad name for a field. It's a reserved word in Access. If you
don't put the [] around it, you could get some strange results. ResponseDate
would be better, but you would need to modify all queries, forms, and reports
that use the DATE field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Brian said:
Hi

How o I make a query so that I can count the number of "yes" responses and
have the query grouped per month - basically want to count the number of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
Click to expand...
Click to expand...
 
G

Guest

Hi John

Is there a way so that it will only display the results for 'Yes'

Thanks

John Spencer said:
In the query grid
Add your date field and response field and response field a second time
Select View Total from the menu
Change GROUP BY under ONE response field to Count
Change the Date field to Format([Date],"yyyy-mm")
Run the query. It will show the number of yeses and the number of noes for
each month

If you aren't concerned about the year than change the format to "mm" (for
2-digit months) or "mmm" for abbreviated months or "mmmm" for full month
names.

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

Brian said:
Hi

How o I make a query so that I can count the number of "yes" responses and
have the query grouped per month - basically want to count the number of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
Click to expand...
Click to expand...
 
G

Guest

Under the response field that has GROUP BY and in the Criteria row put "Yes".
--
KARL DEWEY
Build a little - Test a little


Brian said:
Hi John

Is there a way so that it will only display the results for 'Yes'

Thanks

John Spencer said:
In the query grid
Add your date field and response field and response field a second time
Select View Total from the menu
Change GROUP BY under ONE response field to Count
Change the Date field to Format([Date],"yyyy-mm")
Run the query. It will show the number of yeses and the number of noes for
each month

If you aren't concerned about the year than change the format to "mm" (for
2-digit months) or "mmm" for abbreviated months or "mmmm" for full month
names.

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

Brian said:
Hi

How o I make a query so that I can count the number of "yes" responses and
have the query grouped per month - basically want to count the number of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks and Thanks to John also

KARL DEWEY said:
Under the response field that has GROUP BY and in the Criteria row put "Yes".
--
KARL DEWEY
Build a little - Test a little


Brian said:
Hi John

Is there a way so that it will only display the results for 'Yes'

Thanks

John Spencer said:
In the query grid
Add your date field and response field and response field a second time
Select View Total from the menu
Change GROUP BY under ONE response field to Count
Change the Date field to Format([Date],"yyyy-mm")
Run the query. It will show the number of yeses and the number of noes for
each month

If you aren't concerned about the year than change the format to "mm" (for
2-digit months) or "mmm" for abbreviated months or "mmmm" for full month
names.

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

Hi

How o I make a query so that I can count the number of "yes" responses and
have the query grouped per month - basically want to count the number of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Yes, you can change the other response field to WHERE (instead of group By)
and then enter criteria of YES and that will show only the Yes results.

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

Brian said:
Hi John

Is there a way so that it will only display the results for 'Yes'

Thanks

John Spencer said:
In the query grid
Add your date field and response field and response field a second time
Select View Total from the menu
Change GROUP BY under ONE response field to Count
Change the Date field to Format([Date],"yyyy-mm")
Run the query. It will show the number of yeses and the number of noes
for
each month

If you aren't concerned about the year than change the format to "mm"
(for
2-digit months) or "mmm" for abbreviated months or "mmmm" for full month
names.

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

Brian said:
Hi

How o I make a query so that I can count the number of "yes" responses
and
have the query grouped per month - basically want to count the number
of
"yes' per month.

ID=1
Date= 01/01/2007
Response= Yes

ID=2
Date=01/02/2007
Response=No

ID=3
Date=01/04/2007
Response=Yes

ID=4
Date=02/01/2007
Response=Yes

Thanks
Click to expand...
Click to expand...
Click to expand...
 

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

Access Running Balance in Access 1
Access Count dates within a Month 4
SELECT Statement Question 5
Counts number of entries per day in query 4
Running Balance (Debit and Credit) in Access 2007 4
most recent rows 5
Count(*) with different period 5
Count Records and Group by Month 4

Top