Extract three field values from one record and display them in a combo box! (1 Viewer)

Mike Krailo

Well-known member
Local time
Today, 05:33
Joined
Mar 28, 2020
Messages
1,030
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:
1596984741607.png

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

I’m here to help
Staff member
Local time
Today, 02:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Not sure I understand your question/needs, but have you tried using a UNION query?
 

Micron

AWF VIP
Local time
Today, 05:33
Joined
Oct 20, 2018
Messages
3,476
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

Well-known member
Local time
Today, 05:33
Joined
Mar 28, 2020
Messages
1,030
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:
1596990056902.png
 
Last edited:

Users who are viewing this thread

Top Bottom