Help with end-user defined query

  • Thread starter Thread starter rneaul
  • Start date Start date
R

rneaul

I have a table that captures responses for thirty different questions. Call
the fields Q1-Q30.

I want the end user to be able to query the responses to any of the 30
questions as they choose.
I designed a form the they would input the question number into and then I
thought by clicking on the option button it would activate the query. The
query would then read the data they input in the form, and display the
contents of that field.
I have had no success in getting this to work.
Any suggestions?
 
Dear rneaul:

The keys to your question are in the first and last lines:

"I have a table that captures responses for thirty different
questions. Call the fields Q1-Q30."

"Any suggestions?"

OK, the problem is really in your first statement. Don't put the 30
answers in one record. Put one answer in one record. Your design has
pushed the database into the wrong "shape".

The tools build to manipulate databases are build around the theory
behind databases. And that theory prescribes a "non-spreadsheet-like"
organization. Starting from the table design you now have, add a new
column "QuestionNumber" and put values from 1 to 30 in this column.
Then reduce the Q1-Q30 columns to just one column, Response.

If you have much data already entered, do not dispair. A UNION query
can be used to create the new organization from this.

Rather than describe the interface you have envisioned, please
describe the actual problem you wish to solve with the form. I'll
comment on that later.

I have a table that captures responses for thirty different questions. Call
the fields Q1-Q30.

I want the end user to be able to query the responses to any of the 30
questions as they choose.
I designed a form the they would input the question number into and then I
thought by clicking on the option button it would activate the query. The
query would then read the data they input in the form, and display the
contents of that field.
I have had no success in getting this to work.
Any suggestions?
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
Thanks for the help. I want the form to be able to link to the query, so if
the user puts in Q1, that column gets added to the query, thus being able to
trend by question. If I make the 30questions each a separate record then I
will have 30 rows of data for each survey, and I would then need to figure
out how to program the input form to insert multiple records from one entry.
 
Dear rneaul:

One way to look at this is to use a subform. On the main form you
would select a single respondent. In the subform you would see a list
of the questions with that respondent's answers. So, you WOULD "have
30 separate rows of data for each survey" but you would organize them
together under a single "respondent" in the parent form.

Tom,
Thanks for the help. I want the form to be able to link to the query, so if
the user puts in Q1, that column gets added to the query, thus being able to
trend by question. If I make the 30questions each a separate record then I
will have 30 rows of data for each survey, and I would then need to figure
out how to program the input form to insert multiple records from one entry.
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top