Union query, combine all 4 fields into 1 field

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

 

[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;

 

[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
'====================================================

 

[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"?

 

[efandango]

Thanks John,

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

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

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;

 

[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
'====================================================