Populate a combo box or split a table?

[Joe]

Hello again group. I have posted before and someone has always been able to
lead me to a solution, so here I go again...
Let me say that I know the current "design", if you can call it that, is
terrible. Not my fault, I swear. I inherited this mess and am just trying to
bring it up to speed. I have managed to clean up the majority but there are
this 1 table that is killing me.

THE Table (Process Control) - [PID] is FK to relate to (Parts) table, and
then there are 17 fields which hold "Process Flow" information, as in
[Process 1],[Process 2],...[Process 17]. Of the 17 fields, some are used,
most are not...and I stopped counting the different processes we perform at
40.

Examples:
Part#1, Inspect Blanks, Process Blanks, Certify Parts, Ship to Customer, and
then 13 fields of blanks because that's the entire process for this
particular part, 4 steps.
Part#2, Order Steel, Do this, do that...uses all 17 fields for processing
steps.

What I ultimately want to accomplish is for users to be able to click a
combo-box which is populated with the processes related to a particular part#
and I don't think you can do that with multple fields (and please correct me
if I'm wrong).

So my question would be, can I populate a combo box with values from
multiple fields?(if so, preferably in code)...or do I have the insanely
tedious and time-consuming task of correctly splitting the "Process Control"
table to look forward to for the next 10 years or so?

As always, your input and insight is greatly appreciated. TIA

 

[Damian S]

Hi Joe,

I know your design is terrible, and you do too... I would recommend
reworking it if you can, but in the mean time, you can get data from multiple
fields in your table for a combo box by using a union query:

Select distinct [Process1] as ProcessForCombo from [Process Control]
union
Select distinct [Process2] as ProcessForCombo from [Process Control]
....
union
Select distinct [Process17] as ProcessForCombo from [Process Control]
order by ProcessForCombo

Hope that helps.

Damian.

 

[Joe]

Damian,
First off thanks for the help.
That's getting really close to what I need, the only problem is that the
list is sorted alphabetically and I actually need the Processes to stay in
order..Process 1 should be the first value, Process 2 the 2nd ...etc. I have
tried adjusting and removing the ORDER BY and it doesn't change the outcome,
it keeps alphabetizing the list.
Any ideas?

 

[Joe]

Damian,
I struggled and searched and read for a little while, but your reply did get
me to the answer I needed...

SELECT [Process1] as ProcessForControl FROM [Process Control] WHERE
[Process Control].[PID] = [Form Control PID]
UNION ALL
SELECT [Process2] as ProcessForControl FROM [Process Control] WHERE
[Process Control].[PID] = [Form Control PID]
....
UNION ALL
SELECT [Process17] as ProcessForControl FROM [Process Control] WHERE
[Process Control].[PID] = [Form Control PID];

Apparently the "UNION ALL" doesn't sort the results. (?)

Anyway, thanks a million!!!
Now if I could just figure out how to let the world know that YOU gave me
the right answer (and deserve the credit) then all would be great.
I do believe you saved me a ton of work, so again, thanks a million!!!

 

[Damian S]

No worries Joe,

Glad that it helped. The UNION ALL doesn't sort as it tacks the results on
the end. Another option would have been to add another field into your query
called SortOrder like this:

Select distinct [Process1] as ProcessForCombo, 1 as SortOrder from [Process
Control]
union
Select distinct [Process2] as ProcessForCombo, 2 as SortOrder from [Process
Control]
....
union
Select distinct [Process17] as ProcessForCombo, 17 as SortOrder from
[Process Control]
order by SortOrder, ProcessForCombo

Damian.