Create a query that selects fields based upon a variable

[saraann422]

Good afternoon,

I want to write a query in Microsoft Access 2003.

Let's say I have a database called 'test and it has the fields 'Date', 'A', 'B' and 'C'.

I want to have a query that has a pop up "question", and the user would answer with one of the columns names (A, B or C).

I want my query to choose fields to show based on the answer for "question".

Let's say "question"='A', I would want the query output to be 'Date' and 'A'. (If "question"='B', I would want the query output to be 'Date' and 'B'. etc)

Can someone tell me how to write this, please?

THANK YOU!!

 

[MajP]

What are you actually trying to do? The way you have suggested is not possible. You can write a query to return certain records based on a parameter, but you can not write query to return certain fields based on a parameter. You would have to use vba or build a form to do this.

 

[saraann422]

I have a table that has a field for each question on a survey. I want to create a report based on the criteria entered by the user showing a chart with the trend for the question they entered. I only want the date and one question field to appear on the report. I don't want to create a query for each question, if I don't have to.

Does that make sense? Is it possible?

Thank you!

 

[MajP]

How many questions do you have. The problem is this is a non-normalized design. Questions and responses should be records not individual fields. But that is a pretty common mistake when working with survey databases because they can be difficult to do correctly.

 

[saraann422]

There is one record for each month. There are about 50 fields that correspond to the 50 questions on the form, the answers are a percentage. So my table looks like this:

Date 1.1 1.2 1.3 2.1 2.2 2.3 2.4 etc
1/1/10 90.0 99.1 92.5 96.7 88.4 80.0 100.0 etc
2/1/10 90.0 99.1 92.5 96.7 88.4 80.0 100.0 etc

The form I have to enter the data is really strait forward.

Am I going about this the wrong way? I had thought that multiple sql statements would do the trick, but so far I haven't had any luck.

Thank you for your help!

 

[MajP]

It is a cut and paste job but you need a Union query to bring this back together

say your table looks like this

tblSurvey
RespondantName
Q1
Q2
Q3
....
Q20

You can normalize this data using a Union query

Select
RespondantName
"Q1" as QuestionNumber,
Q1 as Response,
from
tblSurvey
Union
Select
RespondantName
"Q2" as QuestionNumber,
Q2 as Response,
from
tblSurvey
Union
Select
RespondantName
"Q3" as QuestionNumber,
Q3 as Response,
from
tblSurvey
Union
Select
RespondantName
"Q20" as QuestionNumber,
Q20 as Response,
from
tblSurvey

Now your data would look like this

Jones Q1 Yes
Jones Q2 Agree
Jones Q3 Slightly Agree
....
Jones Q20 Disagree
Smith Q1 No
Smith Q2 disagree
Smith Q3 Slightly Agree
....
Smith Q20 Disagree

now you can query this by question, by person, by person and question

So in your case it is something like

select
dtmDate as ResponseDate,
"1.1" as QuestionNumber
1.1 as Percentage
From
tblAnswers
Union
select
dtmDate as ResponseDate,
"1.2" as QuestionNumber
1.2 as Percentage
From
tblAnswers
....
Union
dtmDate as ResponseDate,
"17.3" as QuestionNumber
17.3 as Percentage

Try two unions and see if it works. Once you get two, past it 50 times and change the label in quotes and the field name.

 

[MajP]

So once you get the union query lets call it "qryUnionTestData". You can build a simple form to choose a question number from a combobox, lets call it "cmboQuestion" on form "frmSelectQuestion".

Now you could be a report query.

Select QuestionNumber, Percentage, ResponseDate from qryUnionTestData where QuestionNumber = forms![frmSelectQuestion]![cmboQuestion]

A lot nicer than a parameter query but if you wanted
Select QuestionNumber, Percentage, ResponseDate from qryUnionTestData where QuestionNumber = ["Enter Question Number"]

 

[dhookom]

If you have survey database, you should consider normalizing like At Your Survey[/LINK]

Duane
[url=http://www.access.hookom.net/]Hook'D on Access
MS Access MVP

 

[MajP]

Yes if you are going to be doing this often look at At Your Survey. Survey database design is difficult, because to do it correctly you really have to have a very good understanding of data normalization. Then can often be many-to-many relationships and some times many-to-many-to-many relationships. Therefore, most people design these incorrectly. At Your Survey is pretty flexible, to meet a lot of user needs.

 

[saraann422]

Thank you for all your suggestions! I'll see what I can do now.

 

[saraann422]

Good afternoon,

I was able to create the database using this design. My question now is, can you create a form to use to input the data into the tables..if so, how?

Thank you!
Sarah

 

[dhookom]

At Your Survey does have a form for entering results.

Duane
Hook'D on Access
MS Access MVP