Union query, combine all 4 fields into 1 field

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

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;

--
Duane Hookom
Microsoft Access MVP


"efandango" wrote:

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

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 wrote:
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
...

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



"Duane Hookom" wrote:

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;

--
Duane Hookom
Microsoft Access MVP


"efandango" wrote:

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

Thanks John,

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

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


"John Spencer" wrote:

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;








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 wrote:
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
...

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


efandango wrote:
Thanks John,

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

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


"John Spencer" wrote:

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;







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 wrote:
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
...