Query Table and select Specific Column

[keysrus]

I am new to forums. This is my first post ever. I have been playing with access databases for about 10 years and have survived using advice from forums and google.

I Wanted to go one step further.

Here is my question:

I have a table which has basic user information such as name, position and department followed by about 40 columns which contain yes/no values. It is a training database and boxes on a form are checked to confirm a course has been completed.

I want to be able to query the table and have the users select what field in the table to query based on the value in a combo box on a main menu form. I have created a separate table with the names of all the fields in the main table but I can't get the query to prompt for a specific column. I need to run queries on each column but don't want to created 40+ queries. I have created a basic function which references the combo box on my main menu and does provide the field name but it doesn't return any values. My query column head is Search Criteria: thefieldname() [The function I created] which does return the correct text so my query column shows Search Criteria: The correct field name, but it does not return any data.

Can any one help.

Thanks

Norbert/Keysrus

 

[vbaInet]

40 Yes/No fields does sound like there's a normalisation problem. What are those 40 fields and how do they relate to the table they're in. My guess is some of the fields relate and could be possibly split.

In any case, here's a tutorial on building a search form (and there's a sample db too on there):

http://baldyweb.com/BuildSQL.htm

Welcome to AWF by the way

 

[keysrus]

Thanks for the response and the welcome. The fields in the table all relate to different courses completed by employees and my input form allows me to click a check box to verify yes or no. As long as I can get the employee details and one addtional column [The course I am searching on] then this would be great. I plan to have the user select the column via a form to produce a generic report.

This was orginally a file in excel with columns over several pages and was transferred to access to make it easier to add records. I have heard alot about normalisation but I'm not quite sure what it is or how it would affect my query.

Thanks
Norbert

 

[vbaInet]

It would have signficant effect on how you query and would make your tables more manageable and flexible.

Here are links that should throw more light on the topic:

http://r937.com/relational.html
http://support.microsoft.com/kb/209534
http://www.databasedev.co.uk/database_normalization_process.html

Definitely worth a read. Also have a look in the General section and Theory of Relational Databases section of the forum, perform a search and you will find lots of threads on this too.

Once you've had a rethink about your design, if you have any questions, just post in one of those sections.

 

[jucky68]

40 Yes/No fields does sound like there's a normalisation problem. What are those 40 fields and how do they relate to the table they're in. My guess is some of the fields relate and could be possibly split.

In any case, here's a tutorial on building a search form (and there's a sample db too on there):

http://baldyweb.com/BuildSQL.htm

Welcome to AWF by the way

is it right, man???

 

[vbaInet]

is it right, man???

Reported for link in sig. 1 post. Potential spammer.