12 tables into 1 form

[The_Ernie]

Hello this has become a bit of a problem that i have benn stuck on a couple of days now.
I have 12 tables which are linked to another database but they all have the same feild names within them.
So the problem is how do i get these 12 tables worth of information to appear on the same form as seperate records.

Thanks in Advance.

 

[Mile-O]

Use a UNION query to join the exact same tables.

You can't use the Query Grid Editor as you would a normal query in Access where you click and join the fields and enter criteria.


Here, you need to switch to the SQL view...


An example SQL example would be to join two tables where we have Forename and Surname in tables tblStudents and tblTeachers. We want to return every person whether they are a student or teacher regardless.


The SQL for each table would be something like like:



SELECT tblTeachers.Forename, tblTeachers.Surname
FROM tblTeachers;


and, for the students table


SELECT tblStudents.Forename, tblStudents.Surname
FROM tblStudents;




These two queries have the exact same fields (and these should have the same datatypes i.e. number, text, date/time, etc.


We would UNION these like so:

SELECT tblTeachers.Forename, tblTeachers.Surname
FROM tblTeachers
UNION
SELECT tblStudents.Forename, tblStudents.Surname
FROM tblStudents;


This joins the two tables.

To combine 12 tables you will have 11 unions, obviously.