Merging four fields into one

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I have the follwing SQL:

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

and want to merge
LeaveBy1
LeaveBy2
LeaveBy3
LeaveBy4

into 1 column, [Leavy_By]

so that I just get 2 columns:

Run_No, Leave_By
Run_No, Leave_By
Run_No, Leave_By
Run_No, Leave_By
Run_No, Leave_By
.....



when I try this UNION SQL:

SELECT [Run_No], [LeaveBy1] FROM tbl_Points UNION
SELECT [Run_No], [LeaveBy2] FROM tbl_Points UNION
SELECT [Run_No], [LeaveBy3] FROM tbl_Points UNION
SELECT [Run_No], [LeaveBy4] FROM tbl_Points UNION
UNION SELECT [Run_No], [LeaveBy1] FROM [tbl_Points];


I get an error: "Microsoft Access could not find the object"

what am I doing wrong?
 
I have the follwing SQL:

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

and want to merge
LeaveBy1
LeaveBy2
LeaveBy3
LeaveBy4

into 1 column, [Leavy_By]

so that I just get 2 columns:

Run_No, Leave_By
Run_No, Leave_By
Run_No, Leave_By
Run_No, Leave_By
Run_No, Leave_By
....

when I try this UNION SQL:

SELECT [Run_No], [LeaveBy1] FROM tbl_Points UNION
SELECT [Run_No], [LeaveBy2] FROM tbl_Points UNION
SELECT [Run_No], [LeaveBy3] FROM tbl_Points UNION
SELECT [Run_No], [LeaveBy4] FROM tbl_Points UNION
UNION SELECT  [Run_No], [LeaveBy1] FROM [tbl_Points];

I get an error: "Microsoft Access could not find the object"

what am I doing wrong?

Notice the last and second to last lines in the statement. There is
an extra "UNION". Remove it and try again.
 
Back
Top