Solved Combining two tables to make a list for a combo box. (1 Viewer)

slharman1

Member
Local time
Today, 13:12
Joined
Mar 8, 2021
Messages
467
I have 3 tables (each has an auto number for PK) as follows:

Job table - holds a job # that auto-increments by 1 (not PK) and job name Ect.

Item table - (related to job tbl by FK) misc details about each item on the job.

Acc Table - (related to item tbl by FK) misc details for accessories added to the items.

Every order has at least one item.
But there is not an accessory for every item, some items have acc and some do not. I can make a combo box that holds each job but then I want to create a combo box that lists all items AND their accessories in the list as if an accessory was an item in the list, the same as the items.
so my list would look like this.

first combo box would just list the jobs

second combo box would list items and accessories
Item1
Item2
Item2-1
Item2-2
Item3
Item4
Item5
Item5-1
Item5-2
Item5-3
Ect....
Any help would be appreciated.
thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:12
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure if this is what you want, but look into using a UNION query.
 

plog

Banishment Pending
Local time
Today, 13:12
Joined
May 11, 2011
Messages
11,611
Code:
I want to create a combo box that lists all items AND their accessories in the list as if an accessory was an item in the list, the same as the items.

That sentence started off strong, but then you lost me starting at "as if an accessory was an item int the list, same as the items." I can't parse that. Did you mean:

I want a list of all items and their accessories. And if an item doesn't have an accessory I want it listed as well.

If so the answer is a LEFT JOIN query:

Code:
SELECT Item, Accessory
FROM ItemTable LEFT JOIN AccessoryTable ON ItemTable.ItemID = AccessoryTable.ItemID

That would give you these results:

Item1
Item2-1
Item2-2
Item3
Item4
Item5-1
Item5-2
Item5-3
Ect....

Since Item2 and Item5 have accessories, it would only show with the accessories, not by itself like the other items. For that you would indeed need a UNION query as Dbguy suggested
 

slharman1

Member
Local time
Today, 13:12
Joined
Mar 8, 2021
Messages
467
Code:
I want to create a combo box that lists all items AND their accessories in the list as if an accessory was an item in the list, the same as the items.

That sentence started off strong, but then you lost me starting at "as if an accessory was an item int the list, same as the items." I can't parse that. Did you mean:

I want a list of all items and their accessories. And if an item doesn't have an accessory I want it listed as well.

If so the answer is a LEFT JOIN query:

Code:
SELECT Item, Accessory
FROM ItemTable LEFT JOIN AccessoryTable ON ItemTable.ItemID = AccessoryTable.ItemID

That would give you these results:

Item1
Item2-1
Item2-2
Item3
Item4
Item5-1
Item5-2
Item5-3
Ect....

Since Item2 and Item5 have accessories, it would only show with the accessories, not by itself like the other items. For that you would indeed need a UNION query as Dbguy suggested
Plog
My apologies, my list should’ve looked like this:
Item1
Item2
Item2-1
Item2-2
Item3
Item4
Item5
Item5-1
Item5-2
Item5-3
Ect....
Would you still suggest the left join query?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:12
Joined
May 7, 2009
Messages
19,169
Would you still suggest the left join query?
there was a suggestion of using Union query.
I believe you have to Try it yourself first.
 

Users who are viewing this thread

Top Bottom