Union Query & Combo Box (1 Viewer)

itownson1

Registered User.
Local time
Today, 10:29
Joined
Mar 20, 2019
Messages
43
Morning

I am currently trying (and failing) to merge two tables with 9 identical columns.

In the "Stock" column on both tables it gains its values through a combo box.
When I do the union query, it gives me the stock id (combo - column 1)rather than the stock name (combo - column 2)
SELECT * FROM TBeefLabel
UNION ALL
SELECT * FROM TLabelSausage;

Any help is appreciated. This is the first time I have used union queries as you can probably tell.

Ian
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:29
Joined
Feb 19, 2013
Messages
16,553
in your combo, set the first column width to 0
 

itownson1

Registered User.
Local time
Today, 10:29
Joined
Mar 20, 2019
Messages
43
Hi CJ

Thanks for your response. Both combo boxes are set at 0cm;5cm.
In everything else it finds it ok. It is just the union query it is struggling with.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:29
Joined
Feb 19, 2013
Messages
16,553
how are you struggling? are you getting an error? if so what is the error message?

if you are going to use SELECT * then both tables need exactly the same number of columns and datatypes in the same order

and what do you mean by both combo's? do they both use the same union query?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:29
Joined
Feb 19, 2013
Messages
16,553
just realised - you seem to be using lookups in a table?
 

itownson1

Registered User.
Local time
Today, 10:29
Joined
Mar 20, 2019
Messages
43
Yes, both tables are exactly the same. It is just one column that gets its value from a combo box. The union query is giving me the Id number rather than the stock name.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Jan 23, 2006
Messages
15,364
In the form design, what is the bound column?
 

Attachments

  • ComboBoundColumn.PNG
    ComboBoundColumn.PNG
    11.4 KB · Views: 93

itownson1

Registered User.
Local time
Today, 10:29
Joined
Mar 20, 2019
Messages
43
SELECT [Stock].[ID], [Stock].[Stock] FROM Stock WHERE [Sector]="Beef";
Bound column = 1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:29
Joined
Oct 29, 2018
Messages
21,358
I am currently trying (and failing) to merge two tables with 9 identical columns.
Hi Ian. I'm sure you'll get there in the end, but I was just curious. Why do you have two identical tables?
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Jan 23, 2006
Messages
15,364
If you take the SQL
Code:
SELECT [Stock].[ID], [Stock].[Stock] FROM Stock WHERE [Sector]="Beef";
and put it in the query designer, what result do you get?
Can you post a copy of the database (zip format)?

?? What is table STOCK??
?? What happened to TBeefLabel and TLabelSausage?
?? Why use UNION ALL and not simply UNION??
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:29
Joined
May 7, 2009
Messages
19,169
select (select T.stock from stock as T where T.id=TBeefLabel.stock) as stock, field2, field3 from TBeefLabel
union all
select (select T.stock from stock as T where T.id=TLabelSausage.stock) as stock, field2, field3 from TLabelSausage
 

itownson1

Registered User.
Local time
Today, 10:29
Joined
Mar 20, 2019
Messages
43
Very good question, which I do not have a very good answer for.
I originally kept sausage and other meat separate as more detail was needed for sausage than meat due to more ingredients and production details. I am now thinking I should have found a workaround to put them into one table.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Jan 23, 2006
Messages
15,364
Ian,

Do you have a description/list of your requirements?
It sounds like you are still doing some analysis and design while getting deeply involved in details of SQL and or Access.
Good luck with your project.
 

itownson1

Registered User.
Local time
Today, 10:29
Joined
Mar 20, 2019
Messages
43
JDraw

I have a separate table for stock. Which is a list of all products we do. The combo box in the form is for them to only pick stock we sell. This then puts the product onto the label.
 

Users who are viewing this thread

Top Bottom