Union query, combine all 4 fields into 1 field


E

efandango

I have a union query:

SELECT

tbl_Points.Run_No,
tbl_Points.LeaveBy1,
tbl_Points.LeaveBy2,
tbl_Points.LeaveBy3,
tbl_Points.LeaveBy4

FROM tbl_Points

UNION ALL SELECT
tbl_Points.Run_No,
tbl_Points.LeaveBy1,
tbl_Points.LeaveBy2,
tbl_Points.LeaveBy3,
tbl_Points.LeaveBy4,
FROM tbl_Points;


the results give me:

Run_No Field1 Field2 Field3 Field4
1 data data data date
2 data data data date
3 data data data date
and so on....

How can I get my union to merge field1,2, 3 and 4 into 1 consecutive column,
like this:

Run_No Field
1 data
1 data
1 data
1 data
2 data
2 data
2 data
2 data
3 data
3 data
3 data
3 data
....
 
Ad

Advertisements

D

Duane Hookom

Your union looks like your SELECTs are the same. What's up with that? What's
the name of your union query?

Try something like;
SELECT Run_No, Field1 as FLD
FROM quniQuery
UNION ALL
SELECT Run_No, Field2
FROM quniQuery
UNION ALL
SELECT Run_No, Field3
FROM quniQuery
UNION ALL
SELECT Run_No, Field4
FROM quniQuery;
 
J

John Spencer

I think you want something more like

SELECT Run_No, LeaveBy1 as LeaveBy
FROM tbl_points
UNION ALL
SELECT Run_No, LeaveBy2
FROM tbl_points
UNION ALL
SELECT Run_No, LeaveBy3
FROM tbl_points
UNION ALL
SELECT Run_No, LeaveBy4
FROM tbl_points
UNION ALL

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
E

efandango

thanks Wayne, this worked:

SELECT Run_No, LeaveBy1 as FLD
FROM tbl_Points;
UNION ALL
SELECT Run_No, LeaveBy2
FROM tbl_Points;
UNION ALL
SELECT Run_No, LeaveBy3
FROM tbl_Points;
UNION ALL
SELECT Run_No, LeaveBy4
FROM tbl_Points;

but what did you mean "...looks like your SELECTs are the same"?
 
E

efandango

Thanks John,

Your's worked after I removed the very last line:

'UNION ALL' (which was giving an incomplete Query clause' error.


John Spencer said:
I think you want something more like


my SQL:

SELECT Run_No, LeaveBy1 as LeaveBy
FROM tbl_points;
UNION ALL
SELECT Run_No, LeaveBy2
FROM tbl_points;
UNION ALL
SELECT Run_No, LeaveBy3
FROM tbl_points;
UNION ALL
SELECT Run_No, LeaveBy4
FROM tbl_points;
 
Ad

Advertisements

J

John Spencer

Sorry about that. The perils of copy and paste struck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top