Union Query multiple tables

[Ripper]

Can I use a union query to join multiple tables? They are all exactly the
same in order, file type, etc.
I tried this format:
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM ALEX
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM BM
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM CARL
Union All

I am getting an improper syntax. How can I fix it?

 

[Jeff Boyce]

If your example is actual structure rather than hypothetical, the very first
thing you need to consider is normalizing your data!

If you have one table per student, your tables are not well-normalized. "So
what?", you ask?

So Access' relationally-oriented features and functions work best when they
aren't fed 'sheet data (and your description looks like spreadsheets).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I am getting an improper syntax. How can I fix it?

The UNION ALL goes *between* the SELECT statements; you have two SELECTS cheek
by jowel without a UNION between them, and a hanging UNION at the end. Try

SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM ALEX
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM BM
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM CARL


I hope that this is part of an exercise to normalize your tables - you should
certainly not have multiple tables differing only in tablename!

 

[Ripper]

I don't have a choice in the table design. Our school system uses 1 database
per campus. That is why I have to join them up into 1 table before I can do
anything. Thank the good Lord we are going to 1 database for the district
starting this summer.
--
Thanks As Always
Rip

I am getting an improper syntax. How can I fix it?

The UNION ALL goes *between* the SELECT statements; you have two SELECTS cheek
by jowel without a UNION between them, and a hanging UNION at the end. Try

SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM ALEX
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM BM
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM CARL


I hope that this is part of an exercise to normalize your tables - you should
certainly not have multiple tables differing only in tablename!

 

[Jeff Boyce]

You DO have a choice in your table design ... in Access. What you have
coming in may be uncontrollable, but you needed perpetuate a poor table
structure with your Access database.

If the source (ALEX, BM, CARL, ...) of the records is significant, you'll
need to add a field that indicates the source in each of the SELECT
statements.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't have a choice in the table design. Our school system uses 1
database
per campus. That is why I have to join them up into 1 table before I can
do
anything. Thank the good Lord we are going to 1 database for the district
starting this summer.
--
Thanks As Always
Rip

I am getting an improper syntax. How can I fix it?

The UNION ALL goes *between* the SELECT statements; you have two SELECTS
cheek
by jowel without a UNION between them, and a hanging UNION at the end.
Try

SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM ALEX
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM BM
Union All
SELECT LocID, Course, Teacher, [1], [2], [3], [4], [5], [6]
FROM CARL


I hope that this is part of an exercise to normalize your tables - you
should
certainly not have multiple tables differing only in tablename!