How do I create a query in which the user chooses the last field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one table in which there are first and last name fields and then there
are 6 more fields under which there is just a yes or no answer. users of the
database will want to look at all the names of people who have a yes in one
of the fields, but they need to be able to choose the field they are looking
in each time. can i create a query so that when it is run, the user is
prompted to enter the name of the column they are searching for a yes in (or
is given a drop down box to choose one from) or do i have to create 6
seperate queries?
 
Sounds as if you have a mis-designed data base. If you have to live with the
current design, you might try


SELECT FirstName, LastName
Switch([Which Field] = "YnField1", YNField1,
[Which Field] = "YnField1", YNField1,
[Which Field] = "YnField1", YNField1,
 
Dang nab it, I hate when I hit the wrong key strokes.

SELECT FirstName, LastName
Switch([Which Field] = "YnField1", YNField1,
[Which Field] = "YnField2", YNField2,
[Which Field] = "YnField3", YNField3,
[Which Field] = "YnField4", YNField4,
[Which Field] = "YnField5", YNField5,
[Which Field] = "YnField6", YNField6) as ReturnThis
FROM TheTableName


Ideally, you would have a separate table that would look something like
PersonId: Something that identifies a specific person
QuestionType: Something that identifies what your current fields 1 to 6 contain
Response: Yes or No

Then you could do the query joining the this table to the people table and it
would be straight forward.
 
Queries are really just for retrieving information. If you want to control
how the interface looks, you need a form. The form can be in Datasheet view
if you want it to look like a query.

Then in the Open event of the form, set the ColumnHidden property to True
for the columns you don't want the user to see. One form then handles all
cases.
 
Back
Top