Quarterly Satisfaction Report

[DanRob]

Ok, I'm an Access noob here looking for help.

I have a table that we have been using to track responses we get on patient satisfaction cards. These cards get dropped into comment boxes and have 7 multiple choice questions and an area for comments.



The table has a table like this:

ID	Location	Date Recvd	Site	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Comments
1	box 1	1/23/2013	main	Yes, Always	Yes, Definitely	Yes, Somewhat	No	Yes, Sometimes	Excellent	5	blah
2	box 2	12/14/2011	clinic 1	No	Yes, Somewhat	Yes, Definitely	Yes, Always	No	Poor	7	blah blah

How can I generate a report showing a graph of the count of each response per quarter?

[June7]

What kind of graph - bar, pie, point? All quarters on one graph? Grouped by location or site?

By count of response, you mean how many "Always", how many "Definitely", how many "No", etc?

Query1
SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4) AS YrQtr, Q1 AS Data FROM Table1
UNION SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4), Q2 FROM Table1
UNION SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4), Q3 FROM Table1
UNION SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4), Q4 FROM Table1
UNION SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4), Q5 FROM Table1
UNION SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4), Q6 FROM Table1
UNION SELECT Location, Site, Year([DateRecvd]) & Choose(Month([DateRecvd]),1,1,1,2,2,2,3,3,3,4,4,4), Q7 FROM Table1;

Query2 - use as RowSource for bar graph
TRANSFORM Count(Query1.Site) AS CountOfSite
SELECT Query1.YrQtr
FROM Query1
GROUP BY Query1.YrQtr
PIVOT Query1.Data;