Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-17-2019, 02:55 AM   #1
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 43
Thanks: 5
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Union Query & Combo Box

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

itownson1 is offline   Reply With Quote
Old 09-17-2019, 03:20 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Union Query & Combo Box

in your combo, set the first column width to 0
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-17-2019, 03:24 AM   #3
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 43
Thanks: 5
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Union Query & Combo Box

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.

itownson1 is offline   Reply With Quote
Old 09-17-2019, 03:45 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Union Query & Combo Box

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
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-17-2019, 03:48 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Union Query & Combo Box

just realised - you seem to be using lookups in a table?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-17-2019, 03:51 AM   #6
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 43
Thanks: 5
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Union Query & Combo Box

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.
itownson1 is offline   Reply With Quote
Old 09-17-2019, 04:13 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,398
Thanks: 107
Thanked 2,060 Times in 2,003 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Union Query & Combo Box

In the form design, what is the bound column?
Attached Images
File Type: png ComboBoundColumn.PNG (11.4 KB, 7 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-17-2019, 04:17 AM   #8
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 43
Thanks: 5
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Union Query & Combo Box

SELECT [Stock].[ID], [Stock].[Stock] FROM Stock WHERE [Sector]="Beef";
Bound column = 1
itownson1 is offline   Reply With Quote
Old 09-17-2019, 04:28 AM   #9
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,280
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Union Query & Combo Box

Quote:
Originally Posted by itownson1 View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-17-2019, 04:29 AM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,398
Thanks: 107
Thanked 2,060 Times in 2,003 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Union Query & Combo Box

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??
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-17-2019, 04:30 AM   #11
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,584
Thanks: 68
Thanked 2,755 Times in 2,639 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Union Query & Combo Box

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-17-2019, 04:32 AM   #12
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 43
Thanks: 5
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Union Query & Combo Box

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.
itownson1 is offline   Reply With Quote
Old 09-17-2019, 04:35 AM   #13
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,398
Thanks: 107
Thanked 2,060 Times in 2,003 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Union Query & Combo Box

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-17-2019, 04:37 AM   #14
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 43
Thanks: 5
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Union Query & Combo Box

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.

itownson1 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
UNION / UNION ALL Query Crashed Access LB79 Queries 3 06-11-2017 03:22 AM
Union Query (2013) - qry using union pulling SOME dups dragct Queries 7 11-03-2015 06:06 AM
How to create a Serial Numbers in Union Query from Merged Query’s? sandya Queries 9 05-15-2015 07:56 PM
UNION query MS Jet database engine cannot find the input table or query. casey Queries 9 09-18-2008 10:31 AM
UNION vs. UNION ALL query Baldrick Queries 3 11-26-2001 08:29 AM




All times are GMT -8. The time now is 07:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World