Help with query

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

Guest

I have a form with 10 text boxes into which users enter many different job
operations, and of necessity, not always entered in the same order. I would
like to change the text boxes to combo boxes if I could come up with a query
which would list all unique operations entered into the table, from all 10
text boxes . This query would then be the dropdown list for the combo box
for the user to choose from. Any suggestions?
 
I have a form with 10 text boxes into which users enter many different job
operations, and of necessity, not always entered in the same order. I would
like to change the text boxes to combo boxes if I could come up with a query
which would list all unique operations entered into the table, from all 10
text boxes . This query would then be the dropdown list for the combo box
for the user to choose from. Any suggestions?
Click to expand...

If you are embedding a one (record) to many (job operations)
relationship into a single table, by having ten fields all used for
job operations, *your table structure is incorrectly normalized*. You
could base the Combo Box on a UNION query:

SELECT [JobOperation1] FROM yourtable WHERE [JobOperation1] IS NOT
NULL
UNION
SELECT [JobOperation2] FROM yourtable WHERE [JobOperation1] IS NOT
NULL
UNION
SELECT [JobOperation3] FROM yourtable WHERE [JobOperation1] IS NOT
NULL
UNION
SELECT [JobOperation4] FROM yourtable WHERE [JobOperation1] IS NOT
NULL
UNION
....

This will be slow and inefficient, I fear.

A better design would be to have *THREE* tables: your current main
table (less the ten fields); a table of valid job operations; and a
"resolver" table with the primary key of your current table and
another with the job operation (the actual value or the ID, if the Job
Operations table has a numeric primary key).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top