Select Union Query Help Needed

[laknight]

I have 4 different queries with the same 3 fields which I need to combine but
for some reason I can not get this very simple UNION query to work and I can
not figure out why. It's telling me that there is an expected SQL statement
missing

SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL1
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL2
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL3
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL4
UNION ALL ORDER_BY [PARENT];

 

[Michel Walsh]

The last line is incorrect:

UNION ALL should be followed by a SELECT


Also, it is not clear if you want an ORDER BY clause, of it the field name
is ORDER_BY, as in


UNION ALL SELECT Order_by FROM parent;



Hoping it may help,
Vanderghast, Access MVP

 

[Jerry Whittle]

Remove the last UNION ALL from the last line.

UNION ALL ORDER_BY [PARENT];

Should be:

ORDER_BY [PARENT];

 

[laknight]

Yes, I did that already. I realized the extra UNION ALL was in there after I
copied and pasted into the forum. But now I get another error messgae which
states:

Syntax error in FROM clause

Remove the last UNION ALL from the last line.

UNION ALL ORDER_BY [PARENT];

Should be:

ORDER_BY [PARENT];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have 4 different queries with the same 3 fields which I need to combine but
for some reason I can not get this very simple UNION query to work and I can
not figure out why. It's telling me that there is an expected SQL statement
missing

SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL1
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL2
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL3
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL4
UNION ALL ORDER_BY [PARENT];

 

[John Spencer]

One too many UNION ALL. UNION ALL should only appear BETWEEN the query
statements - not at the beginning nor at the end

SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL1
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL2
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL3
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL4
ORDER_BY [PARENT];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

If Michel or John haven't already suggested the fix, try simplifying the query.

First off remove the ORDER BY at the end and run it.

If that doesn't help, try removing a UNION ALL / SELECT statement pair at a
time and see what happens.

You could also run each of the SELECT statements individually to make sure
that all of them work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Yes, I did that already. I realized the extra UNION ALL was in there after I
copied and pasted into the forum. But now I get another error messgae which
states:

Syntax error in FROM clause

Remove the last UNION ALL from the last line.

UNION ALL ORDER_BY [PARENT];

Should be:

ORDER_BY [PARENT];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have 4 different queries with the same 3 fields which I need to combine but
for some reason I can not get this very simple UNION query to work and I can
not figure out why. It's telling me that there is an expected SQL statement
missing

SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL1
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL2
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL3
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL4
UNION ALL ORDER_BY [PARENT];

 

[John Spencer]

Missed one thing, it should be
ORDER BY [Parent]
Not
ORDER underscore BY [Parent]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

One too many UNION ALL. UNION ALL should only appear BETWEEN the query
statements - not at the beginning nor at the end

SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL1
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL2
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL3
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL4
ORDER_BY [PARENT];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have 4 different queries with the same 3 fields which I need to combine
but
for some reason I can not get this very simple UNION query to work and I
can
not figure out why. It's telling me that there is an expected SQL
statement
missing

SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL1
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL2
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL3
UNION ALL
SELECT [PARENT],[DESCRIPTION],[PRODUCT_LINE] FROM CustomPurch_PartL4
UNION ALL ORDER_BY [PARENT];