• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

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
0
aehrenwo
Asked:
aehrenwo
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aehrenwoTechnology 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now