Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 12:12 PM   #1
Kheribus
Newly Registered User
 
Join Date: Mar 2015
Posts: 84
Thanks: 13
Thanked 0 Times in 0 Posts
Kheribus is on a distinguished road
Multiple Union on Local Excel Linked Tables

I have the following query to union three separate Excel File local linked tables into one query result.

Code:
SELECT regtrips.[Name] As tripName, regtrips.[Schools] As schools, regtrips.[Number Stops] As numStops, regtrips.[Number Assigned] As numAssigned, regtrips.Duration as [duration], regtrips.[Start Time] As startTime, regtrips.[Finish Time] As endTime, regtrips.[Distance] As distance, regtrips.[Bus Type] As busType, regtrips.[Dates] As dateInfo, regtrips.[Description] As description FROM regtrips
UNION SELECT spedtrips.[Name], spedtrips.[Schools], spedtrips.[Number Stops], spedtrips.[Number Assigned], spedtrips.[Duration], spedtrips.[Start Time], spedtrips.[Finish Time], spedtrips.[Distance], spedtrips.[Bus Type], spedtrips.[Dates], spedtrips.[Description] FROM spedtrips
UNION SELECT  2018ESY.[Name], 2018ESY.[Schools], 2018ESY.[Number Stops], 2018ESY.[Number Assigned], 2018ESY.[Duration], 2018ESY.[Start Time], 2018ESY.[Finish Time], 2018ESY.[Distance], 2018ESY.[Bus Type], 2018ESY.[Dates], 2018ESY.[Description] FROM 2018ESY;
The code works to union the regtrips and spedtrips tables, but when I add the second Union for the 2018ESY linked table, I get the following error when trying to view the query in datahseet view:

"Syntax error (missing operator) in query expression '2018ESY.[Name]'.

I have verified that all the column names and data t ypes are the same for all of these tables, so there must just be some stupid syntax error here?

I really appreciate your help!
B

Kheribus is offline   Reply With Quote
Old 06-14-2018, 12:24 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 298
Thanks: 0
Thanked 66 Times in 66 Posts
June7 is on a distinguished road
Re: Multiple Union on Local Excel Linked Tables

Could simplfy by not including table name prefix in front of each field.

Enclose 2018ESY table name in [ ].
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Kheribus (06-14-2018)
Old 06-14-2018, 12:26 PM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,640
Thanks: 10
Thanked 2,072 Times in 2,027 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Multiple Union on Local Excel Linked Tables

A UNION just combines a bunch of individual SELECT statements together. Individually, each SELECT should work on its own. That is how I suggest you debug this.

plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Kheribus (06-14-2018)
Old 06-14-2018, 12:29 PM   #4
Kheribus
Newly Registered User
 
Join Date: Mar 2015
Posts: 84
Thanks: 13
Thanked 0 Times in 0 Posts
Kheribus is on a distinguished road
Re: Multiple Union on Local Excel Linked Tables

June7, encasing the 2018ESY in brackets solved the problem. I wonder why it was necessary to bracket that table but not other tables?

I guess you're right as well that it is not necessary to include the table prefix when listing the fields.

Thanks for your quick help! I try not to come here with my problems too often but you are all so helpful when I do!

Have a great day!
Kheribus is offline   Reply With Quote
Old 06-14-2018, 12:40 PM   #5
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 298
Thanks: 0
Thanked 66 Times in 66 Posts
June7 is on a distinguished road
Re: Multiple Union on Local Excel Linked Tables

Probably because table name begins with number.

June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Linked Tables to local Jaye7 Modules & VBA 5 07-03-2015 01:25 AM
Make Linked Tables Local Newbie2001 Tables 4 12-26-2011 06:20 PM
Linked Tables, Local Tables and ODBC lcbateman3 Tables 2 01-23-2009 04:40 AM
tables changing from linked to local at runtime civmeup General 2 02-19-2008 08:52 PM
Linked Tables to Local Tables Problem jennilewis Tables 0 08-26-2005 01:47 AM




All times are GMT -8. The time now is 11:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World