10 tables one query

[rdkirb49]

I have 10 tables. They are linked to 10 spread sheets. How can I link all ten tables together or to one query? In my query I tried to link them but the only thing that was queried was batch 3. All spreadsheets have an added field called batch. Each batch has a separate mail merge document attached to it (command button). I need a way to query all 10 tables to show which batch a particular SSN is listed. Please help...... Any help is greatly needed and appreciated.

Reg

 

[LittleSmudge]

Mutli thread TimeWaster warning

http://www.tek-tips.com/viewthread.cfm?SQID=879957&SPID=700&page=1

 

[Ed2020]

Do you mean all ten tables are the same structurally (i.e. they contain the same fields, in the same order)? And you want to be able to run a query looking for X records across all ten tables.

If so you need to create a Union query to join all the data together first:

SELECT * FROM TableName1

UNION

SELECT * FROM TableName2

UNION

SELECT * FROM TableName3

etc......


and then run a query on the Union query

SELECT * FROM UnionQueryName
WHERE SSN="Criteria"

HTH,

Ed Metcalfe

Please do not feed the trolls.....

 

[rdkirb49]

Would this code work even if the fields weren't in the same order? Thanks...... Will give it a try.

Reg

 

[Ed2020]

No, the fields have to be in the same order to work.

Ed Metcalfe.

Please do not feed the trolls.....

 

[rdkirb49]

Is there another way?

 

[Ed2020]

Not that I'm aware of.

You could set up a query for each table and arrange the query columns in the right order then run the Union query on the queries.

Ed Metcalfe.

Please do not feed the trolls.....

 

[sha76]

If your fields aren't in the same order you just need to specify the order in the union query.

eg. If you have tbl1 with fields a, b & c and tbl2 with those same fields in the order b, c & a then you would need

SELECT tbl1.a, tbl1.b, tbl1.c
FROM tbl1

UNION SELECT tbl2.a, tbl2.b, tbl2.c
FROM tbl2;

"Your rock is eroding wrong." -Dogbert

 

[rdkirb49]

Thanks...