How do I express more than 2 SELECT FIELDS within the same table?

[Guest]

Hello anyone

Can you help me with solve my UNION query problem. I want to make a 'tall'
table from multiple sets of fields contained within the same table, the
sample below works fine...

SELECT [Run_Point_Venue_A], [Run_Point_Address_A]
FROM Run_Points

UNION SELECT [Run_Point_Venue_C], [Run_Point_Address_C]
FROM Run_Points;

....but when i want to add additional Select fields, such as the following:

UNION SELECT [Run_Point_Venue_D], [Run_Point_Address_D]
FROM Run_Points;
UNION SELECT [Run_Point_Venue_E], [Run_Point_Address_F]
FROM Run_Points;
UNION SELECT [Run_Point_Venue_G], [Run_Point_Address_G]
FROM Run_Points;

etc, etc.

the query fails with various messages depending on what syntax i experiment
with.

i have looked in the manual and online for help on using more than two
'instances' of select, but can't find the asnwer.

 

[Guest]

You need to remove the semi-colon from the end of each of the SELECT queries.
You should only have one semi-colon in the query, and you don't really even
need that (Access will add it).

 

[jinsungy]

it should simply be..

SELECT [Run_Point_Venue_A], [Run_Point_Address_A]
FROM Run_Points
UNION SELECT [Run_Point_Venue_C], [Run_Point_Address_C]
FROM Run_Points
UNION SELECT [Run_Point_Venue_D], [Run_Point_Address_D]
FROM Run_Points
UNION SELECT [Run_Point_Venue_E], [Run_Point_Address_F]
FROM Run_Points
UNION SELECT [Run_Point_Venue_G], [Run_Point_Address_G]
FROM Run_Points;

www.dbwiz.com

 

[John Vinson]

the query fails with various messages depending on what syntax i experiment
with.

It might help if you would post the syntax and the message...

You'll get "Query Too Complex" if the entire query runs to over
64KBytes, compiled - but you should be able to get all 24 (?) SELECTs
in there. As suggested, try removing the semicolons, though in fact I
believe that they are allowed.

John W. Vinson[MVP]