Column Query

[blindlemonray]

I have a table with headings like this:-

Client Code user1 user2 user3
A A1 20 7
B B2 9 5
C C3 5 10

I would like to query this table but end up with Client, Code and a user of my choice. I know I can enter sqaure brackets in a query and then enter my own parameter but I would like to enter my own parameter for the column I want to be displayed. So when i run the query it would ask me "which user?" I type user1 and end up with some like:-

Client Code user1
A A1 20
C C3 5

any pointers would be appreciated!
Thanks in advance...

 

[Golom]

This (because your table is un-normalized) makes for messy SQL. I recommend that you construct a table of the form
[tt]
myTable
Client Code UserNumber UserValue
A A1 1 20
A A1 2
A A1 3 7
etc.
[/tt]
You can populate it from your existing table with code of the form

Select Client, [Code], 1 As UserNumber, User1 As [User]
UNION ALL
Select Client, [Code], 2 As UserNumber, User2 As [User]
UNION ALL
Select Client, [Code], 3 As UserNumber, User3 As [User]

Then your query to retrieve data us just

Select Client, [Code], [User]
From myTable
Where UserNumber = [Enter a User Number]

 

[blindlemonray]

Most excellent.. thanks very much Golom. i just received the table in that form and had not thought to outside of the box and had just tried to work with as is.. i had created a long work around but this is so much better.. many thanks

 

[lespaul]

I didn't think access supported stored procedures.....

Leslie

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

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design