Query question

[tuxalot]

I have a query that produces data like this:

name		date	q1	q2	q3	q4	q5
Subject A	3/1/08	1	2	1	2	1
Subject A	5/3/09	1	2	1	1	2
Subject A	6/4/10	2	1	2	2	2
Subject B	4/5/08	1	1	2	1	2
Subject C	2/7/09	1	2	2	2	1
Subject C	3/8/10	1	2	1	2	1

q1-100 represent responses to questions. I am only interested in responses = 2 for consecutive years. So I would like to feed the above into a second query to produce this result:

name		date	q1	q2	q3 q4	  q5
Subject A	3/1/08		2				
Subject A	5/3/09		2		        2	
Subject A	6/4/10				         2	
Subject B	4/5/08						
Subject C	2/7/09		2		2		
Subject C	3/8/10		2		2

Any ideas how I can achieve this? Thanks for any assistance.

 

[dhookom]

I would probably build a query from your raw data and then feed it into a crosstab. It would help if you provided your actual table structure and sample records.

Duane
Hook'D on Access
MS Access MVP

 

[tuxalot]

Thanks Duane. What I am working on is a modification of your At Your Survey DB. I've attached a relationship report (PDF). What is the preferred way to provide sample records here in the forum?

 

[tuxalot]

Here is the SQL for the query.

PARAMETERS [forms]![frmSurveyResponses]![sfrmReportSelect].[Form]![cboSrvID] Long;
TRANSFORM First(tblResponses.Rspns) AS FirstOfRspns
SELECT tblHotelInfo.HName AS [Hotel Name], tblSrvRspns.SurveyStartDate AS [Survey Date]
FROM tblHotelInfo INNER JOIN (tblSrvRspns INNER JOIN (tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID = tblResponses.QstnID) ON tblSrvRspns.RspnsID = tblResponses.RspnsID) ON tblHotelInfo.HotelID = tblSrvRspns.HID
WHERE (((tblQuestions.SrvID)=[forms]![frmSurveyResponses]![sfrmReportSelect].[Form]![cboSrvID]))
GROUP BY tblHotelInfo.HName, tblSrvRspns.SurveyStartDate
ORDER BY tblHotelInfo.HName, tblSrvRspns.SurveyStartDate
PIVOT tblQuestions.QstnBrief;

 

[dhookom]

I would probably create a query based on tblSrvRspns with a subquery that is based on the same hotel and question but previous/next year results. You can filter for 2 and count results or similar.

Duane
Hook'D on Access
MS Access MVP

 

[tuxalot]

Thanks Duane. Could you possibly provide a code example to point me in the right direction? I am still getting my head wrapped around Access.