Combine results using query

[tomeck]

I have loaded survey results from an on-going bilingual survey and need to
combine the results of the English and Spanish questions before running a
crosstab query. For example, I have a column for gender that is from the
english question and one form the Spanish quesiton. I know I can create an
update query and update a third column based upon these two columns, but what
i want to do is to avoid adding this third column because it would need to be
updated each month when i add more data.

Is there a way to prepare a cross-tab query that combines two columns into
one prior to actually doing the cross-tab?

(I am a fairly novice Access user ...)

thanks.

 

[KARL DEWEY]

I would need to see sample data but I expect it can be done.
Is the data in one table or 2? If 2, then is there common fields such as
question numbers and all other fields an exact duplicate?

Post sample data of both. Post what you would want the results to look like.

 

[tomeck]

The data is all in one table.

When a respondent takes a survey, they select a language. Depending upon
the selection, they were routed to different forms. For example, if they
selected English they would then answer Questions 1, 2 and 3. If they
selected Spanish, they would answer Questions 4,5 and 6. Q1 is the same as
Q4 except for the language it is written in. The respondent has one row in
the table; language and Questions 1 to 6. If Q 1, 2 and 3 have data, then
Q4,5 and 6 would be blank.

What I want to do is to cross tab the combination of Q1 and 4 with the data
they survey was completed. I can set set up a crosstab query of Q1 and date
or Q4 and date, but what ideally I want a crosstab of the combination with
date.

Thanks.

Typical CSV file of what data would look like
Language, q1,q2,q3,q4,q5,q6,date
1,2,1,3, , , ,August
1,3,2,4, , , ,May
2, , , ,2,1,3,August
2, , , ,3,2,4,May

Desired result for cross tab of Q1 and date
May August
q1=1 0 0
q1=2 2 2
q1=3 0 0

Desired results for crosstab of Q2 and date
May August
q2=1 0 2
q2=2 0 0
q2=3 2 0

 

[KARL DEWEY]

Your table structure is wrong. Search on 'Survey' as there is much
information on better ways to do it.
Your data has to be rearranged using a union query, then crosstab --
Tomeck_1 --
SELECT "Question 1" AS [Question], Tomeck.Q1 AS [Response], Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=1))
UNION ALL SELECT "Question 2" AS [Question], Tomeck.Q2 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=1))
UNION ALL SELECT "Question 3" AS [Question], Tomeck.Q3 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=1))
UNION ALL SELECT "Question 1" AS [Question], Tomeck.Q4 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=2))
UNION ALL SELECT "Question 2" AS [Question], Tomeck.Q5 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=2))
UNION ALL SELECT "Question 3" AS [Question], Tomeck.Q6 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=2));


TRANSFORM Count(Tomeck_1.[Response]) AS CountOfResponse
SELECT Tomeck_1.[Question], Tomeck_1.Response
FROM Tomeck_1
GROUP BY Tomeck_1.[Question], Tomeck_1.Response
PIVOT Tomeck_1.[QuestDate];

 

[tomeck]

Thanks - that is what i was afraid of.

Your table structure is wrong. Search on 'Survey' as there is much
information on better ways to do it.
Your data has to be rearranged using a union query, then crosstab --
Tomeck_1 --
SELECT "Question 1" AS [Question], Tomeck.Q1 AS [Response], Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=1))
UNION ALL SELECT "Question 2" AS [Question], Tomeck.Q2 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=1))
UNION ALL SELECT "Question 3" AS [Question], Tomeck.Q3 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=1))
UNION ALL SELECT "Question 1" AS [Question], Tomeck.Q4 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=2))
UNION ALL SELECT "Question 2" AS [Question], Tomeck.Q5 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=2))
UNION ALL SELECT "Question 3" AS [Question], Tomeck.Q6 AS [Response],
Tomeck.QuestDate
FROM Tomeck
WHERE (((Tomeck.Language)=2));


TRANSFORM Count(Tomeck_1.[Response]) AS CountOfResponse
SELECT Tomeck_1.[Question], Tomeck_1.Response
FROM Tomeck_1
GROUP BY Tomeck_1.[Question], Tomeck_1.Response
PIVOT Tomeck_1.[QuestDate];

--
Build a little, test a little.

The data is all in one table.

When a respondent takes a survey, they select a language. Depending upon
the selection, they were routed to different forms. For example, if they
selected English they would then answer Questions 1, 2 and 3. If they
selected Spanish, they would answer Questions 4,5 and 6. Q1 is the same as
Q4 except for the language it is written in. The respondent has one row in
the table; language and Questions 1 to 6. If Q 1, 2 and 3 have data, then
Q4,5 and 6 would be blank.

What I want to do is to cross tab the combination of Q1 and 4 with the data
they survey was completed. I can set set up a crosstab query of Q1 and date
or Q4 and date, but what ideally I want a crosstab of the combination with
date.

Thanks.

Typical CSV file of what data would look like
Language, q1,q2,q3,q4,q5,q6,date
1,2,1,3, , , ,August
1,3,2,4, , , ,May
2, , , ,2,1,3,August
2, , , ,3,2,4,May

Desired result for cross tab of Q1 and date
May August
q1=1 0 0
q1=2 2 2
q1=3 0 0

Desired results for crosstab of Q2 and date
May August
q2=1 0 2
q2=2 0 0
q2=3 2 0