Help with syntax error in qry Select statement 1

[KerryL]

I keep getting a syntax error when I try and run this query. What have I done wrong?


SELECT * FROM ServicesAdrian-Jxn UNION
SELECT * FROM ServicesAnnArbor UNION
SELECT * FROM ServicesBattleCreek UNION
SELECT * FROM ServicesBayCity-Midland UNION
SELECT * FROM ServicesBentonHarbor UNION
SELECT * FROM ServicesBigRapids UNION
SELECT * FROM ServicesCaro UNION
SELECT * FROM ServicesDetroitEast UNION
SELECT * FROM ServicesDetroitWest UNION
SELECT * FROM ServicesDisMgmt UNION
SELECT * FROM ServicesOakland-OakPark UNION
SELECT * FROM ServicesPortHuron UNION
SELECT * FROM ServicesSaginaw UNION
SELECT * FROM ServicesWayne UNION;

 

[PHV]

Replace this:
SELECT * FROM ServicesWayne UNION;
By this:
SELECT * FROM ServicesWayne;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

I removed the last "UNION" but I'm still getting this error:

"Syntax error in FROM clause

 

[lespaul]

you may need to enclose the tables with "-" in [brackets]

leslie

Leslie

 

[KerryL]

Leslie,
It looks like you were right on target.
I added brackets to the tables containing a dash (see below) and it ran perfectly. Thank you!

SELECT * FROM [ServicesAdrian-Jxn] UNION
SELECT * FROM ServicesAnnArbor UNION
SELECT * FROM ServicesBattleCreek UNION
SELECT * FROM [ServicesBayCity-Midland] UNION
SELECT * FROM ServicesBentonHarbor UNION
SELECT * FROM ServicesBigRapids UNION
SELECT * FROM ServicesWayne UNION;

 

[Jhongski]

You can try this logic below:
SELECT * FROM ServicesAdrian-Jxn
UNION
(SELECT * FROM ServicesAnnArbor)
UNION
(SELECT * FROM ServicesBattleCreek)
UNION
(SELECT * FROM [ServicesBayCity-Midland])

The logic here is to separate the SELECT statements from the UNION by using(). One more thing, my assumption here is that all the tables that are included in your UNION statements have the same number of fields specially that you're using the SELECT * syntax.

 

[KerryL]

Okay, I have a dumb question.

The query runs fine now. It finds 7600 records and shows the results of the query, but it's not appending them to the Master table.

Don't I need to add a line that says "INSERT INTO tblMaster" or something like that?

As it is the query is showing me its results but not appending the results to the target table.

 

[PHV]

Create an append query based on your saved union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244