Query based on user input

[Knape97]

Hi Guys.

Not sure if this is possable, and I have absolutly no idea how to go about what I need doing so fingers crossed you can help

(The stuff I will put here is a lot more simplified than my actual system, but it should explain what I require)

In a Table (Table1) I have the following Fields; UserID, UserName, Test0405, Test0506, Test0607.

I need a query that will pull off The UserID, UserName and one of the test dates. However, what I need is a method by which Access will prompt the user asto which Test field is used...

i.e.
The user runs the query and a input promp appears. If the user enters "0405" the query returns the fields UserID, UserName, Test0405. If the user enters "0506" the query returns UserID, UserName, Test0506, etc...

Any help you guys can give me would be greatly appreciated.

 

[PHV]

I'd suggest either normalising your database schema or play with a saved union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I agree with PHV. A temporary patch using a union query would look something like:
SELECT UserID, UserName, Test0405 as TestScore, #04/05/2005# as TestDate
FROM Table1
UNION ALL
SELECT UserID, UserName, Test0506, #05/06/2005#
FROM Table1
UNION ALL
SELECT UserID, UserName, Test0607, #06/07/2005#
FROM Table1;

You can then use a regular select query. I'm not sure how your field names map to dates.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

I would also question why you have both the UserID and UserName in Table1. If you have a table USERS that contains both of these pieces of data, then you only need the ID in the other table as a FK to the USERS table.

You may want to read up on data normalization, The Fundamentals of Relational Database Design

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual