CODEWITHSUNDEEP
sqlms-accessms-access-2010
Neptuno129
Neptuno129

Reputation: 13

Include column with field name on union query

I have a table in access that has all the criteria of my products and in need to create a query that will put all information about the criteria in one single column. For this I used a Union query. Now I would like to have a the field name of the criteria on a column next to it.

Is it possible to get this result?

Sorry for my lack of knowledge to put this post in a poor format and thank you for any kind of help you may be able to give me.

My table has the following structure:

+------+--------------+--------------+--------------+-------------+
| P/N  |  Criteria 1  |  Criteria 2  |  Criteria 3  |  Criteria 4 |
+------+--------------+--------------+--------------+-------------+
| 12A  |  blue        |  plastic     |  Box         |  5mm        |
| 20C  |  neutral     |  metal       |  Pack        |  120mm      |
+------+--------------+--------------+--------------+-------------+

I have a union Query in Access with the following result:

+------+------------+
| P/N  |  Criteria  |
+------+------------+
| 12A  |    blue    |
| 12A  |    plastic |
| 12A  |    Box     |
| 12A  |    5mm     |
| 20C  |    neutral |
| 20C  |    metal   |
| 20C  |    pack    |
| 20C  |    120mm   |
+------+------------+

I want the final result I want is table in this format:

+------+-----------+----------------+
| P/N  |  Criteria |  Criteria Name |
+------+-----------+----------------+
| 12A  |  blue     |  Criteria 1    |
| 12A  |  plastic  |  Criteria 2    |
| 12A  |  Box      |  Criteria 3    |
| 12A  |  5mm      |  Criteria 4    |
| 20C  |  neutral  |  Criteria 1    |
| 20C  |  metal    |  Criteria 2    |
| 20C  |  pack     |  Criteria 3    |
| 20C  |  120mm    |  Criteria 4    |
+------+-----------+----------------+

Upvotes: 1

Views: 167

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

Assuming that your union query looks something like this -

select t.[p/n], t.[criteria 1] as criteria
from yourtable t
union all
select t.[p/n], t.[criteria 2] as criteria
from yourtable t
union all
select t.[p/n], t.[criteria 3] as criteria
from yourtable t
union all
select t.[p/n], t.[criteria 4] as criteria
from yourtable t

Then you can add the field name like this:

select t.[p/n], t.[criteria 1] as criteria, 'Criteria 1' as [Criteria Name]
from yourtable t
union all
select t.[p/n], t.[criteria 2] as criteria, 'Criteria 2' as [Criteria Name]
from yourtable t
union all
select t.[p/n], t.[criteria 3] as criteria, 'Criteria 3' as [Criteria Name]
from yourtable t
union all
select t.[p/n], t.[criteria 4] as criteria, 'Criteria 4' as [Criteria Name]
from yourtable t

Upvotes: 1

Related Questions