Extract three field values from one record and display them in a combo box!

[Mike Krailo]

Any ideas how to do this. I have records that store the EmpID for each of three different commission types. The total commission available can be split up to three ways depending on who did the Sales, Estimating, and Project Management for a particular job record. How do I extract those EmpID's from the one record and then show the actual names in a combo box. This will be used to display the final commission earned based on which Employee is selected in the combo. The combo could show 1, 2 or up to 3 names in the dropdown. I'm trying to limit the combo to only show those emplyoee's that were assigned in the single job record.

Right now I just get the simple select of all three columns that look like this:

In this case EmpID 2 gets the Sales commission only, while EmpID 1 gets both the Estimating and Proj Managment commission. I need to get those three values into one column. Any ideas?

 

[theDBguy]

Hi. Not sure I understand your question/needs, but have you tried using a UNION query?

 

[Deleted member 147267]

If that is how your records look, then the tables look to be improperly designed. If I'm correct, then you wouldn't have this or other future problems if they were properly normalized. As such, you will have to employ a work-around; perhaps a Union query. However, each piece of data has a different meaning because each field name is different, yet you want to amalgamate them into one combo list. Thus the Union field name will need to be something that relates them all.

You'd be better off normalizing your tables and doing simple Select queries, plus you will have issues if you need to add a 4th type.

 

[Mike Krailo]

Micron, I think you are correct. I will rework the tables to remove those three fields and use a separate table joined by JobID. That should work much better. Thanks for pointing that out.

Here is the new table structure I came up with: