UNION queries

[JoD]

Is there a bug in Access 2007? I have constructed a UNION query with 5 parts,
selecting the same number of fields from a table but with different rows
based on different criteria, as follows:
SELECT col1, col2, col3...col5 AS [common name1], col6 AS [common name2]
....
UNION ALL
SELECT col1, col2, col3...col10 AS [common name1], col11 AS [common name2]
....
UNION ALL
SELECT col1, col2, col3...col15 AS [common name1], col16 AS [common name2]
....
UNION ALL
SELECT col1, col2, col3...col20 AS [common name1], col21 AS [common name2]
....
UNION ALL
SELECT col1, col2, col3...col25 AS [common name1], col26 AS [common name2]
....;

Columns 5,10,15,20 and 25 are all the same type as are columns 6,11,16,21,26

When I run the query, I get prompted to enter the parameter for column 5,
just as if I planned to enter the value dynamically, even f I remove the
brackets and enter just a single word after the AS keyword. However , if I
remove the AS [common name1], the AS [common name2] functions correctly,
renaming the column to common name2.

TIA for any assistance.

 

[Jerry Whittle]

Instead of brackets, try single or double quotes.

 

[JoD]

I tried both single and double quotes with no behavioral change. This isn't a
show-stopper obviously, just a head scratcher.

All I have to do is remove the first AS [common name1] (or 'common name1' or
"common name1") and the query runs with no difficulty, assigning common name2
correctly.

The query does not reference col5,col10,col15,col20 or col25 anywhere except
in the SELECT list, common name1 is not in the underlying table field list or
the field list of the query that the select statements are run against.

Instead of brackets, try single or double quotes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Is there a bug in Access 2007? I have constructed a UNION query with 5 parts,
selecting the same number of fields from a table but with different rows
based on different criteria, as follows:
SELECT col1, col2, col3...col5 AS [common name1], col6 AS [common name2]
...
UNION ALL
SELECT col1, col2, col3...col10 AS [common name1], col11 AS [common name2]
...
UNION ALL
SELECT col1, col2, col3...col15 AS [common name1], col16 AS [common name2]
...
UNION ALL
SELECT col1, col2, col3...col20 AS [common name1], col21 AS [common name2]
...
UNION ALL
SELECT col1, col2, col3...col25 AS [common name1], col26 AS [common name2]
...;

Columns 5,10,15,20 and 25 are all the same type as are columns 6,11,16,21,26

When I run the query, I get prompted to enter the parameter for column 5,
just as if I planned to enter the value dynamically, even f I remove the
brackets and enter just a single word after the AS keyword. However , if I
remove the AS [common name1], the AS [common name2] functions correctly,
renaming the column to common name2.

TIA for any assistance.