Access SQL - Consolidating multiple queries into 1 - UNION

I have have a table with 8 fields for Products (i.e. Product1, Product 2). There isn't always a value in each field per line item.
I created separate query for each Product field.

SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product1 AS Product
FROM RAMP2015
WHERE (((RAMP2015.RAMP2015Product1) Is Not Null));

Open in new window


There are 8 of these now. And then I created the following code to Union them all together.

SELECT RAMPID, Product
FROM Product1

UNION SELECT RAMPID, Product
FROM Product2

UNION SELECT RAMPID, Product
FROM Product3

UNION SELECT RAMPID, Product
FROM Product4

UNION SELECT RAMPID, Product
FROM Product5

UNION SELECT RAMPID, Product
FROM Product6

UNION SELECT RAMPID, Product
FROM Product7

UNION SELECT RAMPID, Product
FROM Product8

Open in new window


Is there away to do this all in one SQL statement? So I dont have 8 Product queries appearing in the user view?

Thanks,

Adam
Adam EhrenworthLead Technology AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use a Crosstab query to do something of this nature. If you're doing this in Access you can use the Query Wizard to do it.

A better solution, however, would be to normalize your data. Anytime you see a table with repeating, numbered columns you're dealing with data being stored incorrectly.
0
PatHartmanCommented:
As Scott has already mentioned, Products belong in a separate table.  That way it doesn't matter if you have 0 or a hundred.  The correct table design accommodates any number of related records including 0.  What you have is known as a repeating group which is used all the time in spreadsheets but never used in relational databases because it violates first normal form.  Do some reading on normalization to help you get your head around the new schema.  Once you store the data correctly, working with it will be easier.  The biggest change will be to your form which will go from having 8 separate controls for product to having a subform where as many products as needed can be entered.

The union query is the only way to take 8 separate lists and stack them on top of each other to make a single recordset.  You only need one query though.  The 8 subqueries can be coded within the union query.  If my subqueries are complex or have joins, I tend to create them as separate querydefs but if they are simple and I only need a couple of columns, I code the selects inside the union.  But, the sooner you fix the real problem, the sooner you can be done with it.
0
Nick67Commented:
There's an excellent normalization tutorial here
http://www.phlonx.com/resources/nf3/

Your code samples do suggest that perhaps you need to revisit the data design
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product1 AS Product
FROM RAMP2015

There's real hell to be unleashed in trying to move ahead with an RDBMS system of any kind when your data design is not at least mostly normalized.  The suffixes and RAMP And Product suggest you don't have that fundamental requirement beat into shape.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Adam EhrenworthLead Technology AnalystAuthor Commented:
Thanks for the feedback. I didn't design this database structure... originally there were only 2 products fields needed. If you can provide an example of how the code can all be done at once that would at least help me short term.

Do you create temp tables for each query to accomplish this?
0
Nick67Commented:
You can try stuff like

select * from (
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product1 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product2 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product3 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product4 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product5 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product6 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product7 AS Product
FROM RAMP2015
Union
SELECT RAMP2015.ID AS RAMPID, RAMP2015.RAMP2015Product8 AS Product
FROM RAMP2015
) as AllProducts
WHERE AllProducts.Product is not Null;

You build a derived table in the query with the Unions, and then take all the non-nulls.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
In your case, you did not want the nulls, and you started with UNION
UNION selects only distinct values by default
UNION ALL is used where selection of all the values is required.

I hope this helps in both the short and long term!
0
PatHartmanCommented:
originally there were only 2 products fields needed
Once you have more than one, you have many and falling into the trap of using a repeating group only leads to later rework.

You can find any number of examples that show you how to work with 1-many relationships.  Start with Northwinds.

Resorting to union queries works for reports but not for forms since union queries are not updateable.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.