Union query with Syntax Error in From Clause

[sbrokate]

HI,

I've been trying to get this query working for a couple of days now, but no
luck. Can anyone tell me what I am doing wrong?

I have three tables that I need to combine all the data into one table to run
reports off of. Table names are Authorizations1, Authorizations2, and
Authorizations3. All three have same data types and fields. If I have just
one table listed, the query works, but once I load the second table to the
query, I get the Syntax Error in From Clause. Checked table name spellings,
etc and all are good to go.

SELECT Authorizations2.FY, Authorizations2.UnitCode, Authorizations2.SIC,
Authorizations2.AuthDate, Authorizations2.Amount, Authorizations2.AuthNote
FROM Authorizations2
UNION SELECT Authorizations2.FY, Authorizations2.UnitCode, Authorizations2.
SIC, Authorizations2.AuthDate, Authorizations2.Amount, Authorizations2.
AuthNote
FROM Authorizations2

SELECT Authorizations3.FY, Authorizations3.UnitCode, Authorizations3.SIC,
Authorizations3.AuthDate, Authorizations3.Authorization, Authorizations3.
AuthNote
FROM Authorizations3
UNION SELECT Authorizations3.FY, Authorizations3.UnitCode, Authorizations3.
SIC, Authorizations3.AuthDate, Authorizations3.Authorization, Authorizations3.
AuthNote
FROM Authorizations3;

Thanks
Sharon

 

[Jeff Boyce]

Sharon

If "all three have the same data types and fields", why bother using MS
Access? That description sounds like how you'd keep track if you were
limited to a spreadsheet.

How about using a SINGLE table with those data types and fields, plus one
more field to hold [Authorization#].

Unless, of course, you have (up to) 3 authorizations needed and that's how
you are storing whether there's one, two or three (in which case, look back
above re: spreadsheets!). If you can have none to three authorization,
that's a one-to-many relationship that needs another table if you are to get
the best use of Access' relationally-oriented features/functions.

Good Luck!


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[sbrokate]

Thanks Jeff,

The problem is that we have 3 users on the same program at this time on a
server which slows Access down to a snail pace (It's a large server, with
many, many departments using it). It currently has one table that all 3 will
load data to at the same time.

I created this new program and have each person with their own version on
their hard drive that updates their data to a separate table on the server.
Now I just need to get all three tables combined into one so I can run
reports from that one table.

I thought about pulling the data from the three tables and tried to create
queries to get everything running as I need it to, but couldn't get it to
work. I read about the union query and thought that was the best way to go.

I also don't have the permissions necessary to load a project to the server.
All data needs to be available on the server for backup purposes.

So, any suggestions? I'm starting to think we'll have to go back to Excel
and lose the reporting functionality we currently have with Access.

Thanks,
Sharon

Sharon

If "all three have the same data types and fields", why bother using MS
Access? That description sounds like how you'd keep track if you were
limited to a spreadsheet.

How about using a SINGLE table with those data types and fields, plus one
more field to hold [Authorization#].

Unless, of course, you have (up to) 3 authorizations needed and that's how
you are storing whether there's one, two or three (in which case, look back
above re: spreadsheets!). If you can have none to three authorization,
that's a one-to-many relationship that needs another table if you are to get
the best use of Access' relationally-oriented features/functions.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

HI,

[quoted text clipped - 34 lines]

Thanks
Sharon

 

[KARL DEWEY]

Try this ---
SELECT Authorizations1.FY, Authorizations1.UnitCode, Authorizations1.SIC,
Authorizations1.AuthDate, Authorizations1.Amount, Authorizations1.AuthNote
FROM Authorizations1
UNION ALL SELECT Authorizations2.FY, Authorizations2.UnitCode,
Authorizations2.
SIC, Authorizations2.AuthDate, Authorizations2.Amount, Authorizations2.
AuthNote
FROM Authorizations2
UNION ALL SELECT Authorizations3.FY, Authorizations3.UnitCode,
Authorizations3.
SIC, Authorizations3.AuthDate, Authorizations3.Authorization, Authorizations3.
AuthNote
FROM Authorizations3;

 

[sbrokate]

Worked! Thank you, thank you, thank you.

Try this ---
SELECT Authorizations1.FY, Authorizations1.UnitCode, Authorizations1.SIC,
Authorizations1.AuthDate, Authorizations1.Amount, Authorizations1.AuthNote
FROM Authorizations1
UNION ALL SELECT Authorizations2.FY, Authorizations2.UnitCode,
Authorizations2.
SIC, Authorizations2.AuthDate, Authorizations2.Amount, Authorizations2.
AuthNote
FROM Authorizations2
UNION ALL SELECT Authorizations3.FY, Authorizations3.UnitCode,
Authorizations3.
SIC, Authorizations3.AuthDate, Authorizations3.Authorization, Authorizations3.
AuthNote
FROM Authorizations3;

HI,

[quoted text clipped - 27 lines]

Thanks
Sharon