linked databases query

[vicsaccess]

Hello all and in advance thank you once again for your help.
i have several databases on a shared network, one for each location in our buisiness unit. what i am forseeing is that for the lead in this department i was thinking of giving a database that is linked to each individual database, that part is simple. my problem comes with the queries. each database has three tables lets say. they track the shift in the first, the object consumed in the second and the objects created from the first master object in the third. this works great in the individual databases because i have the ID's set up and it all links great in the queries. if i link all the different databases into a common database for the lead to be able to monitor the days activity how do i work the queries since each is identical just from a different location. surely someone here is currently doing this and has some do's and don'ts.

[pbaldy]

I would likely have used a single database, with a field in the table for location. Your solution is probably a UNION query to pull all the data together, which basically puts in the the same format (ie it normalizes the data).

[vicsaccess]

i used a make table query to add the location to each table but i'm unfamiliar with union queries. how do i use a union query? what i envision is to bring all the tables for shifts brought together with the individual locations attached, all the consumed brought together with the locations etc.

[pbaldy]

SELECT Field1, Field2, "Location1" As Location
FROM Location1Table
UNION ALL
SELECT Field1, Field2, "Location2" As Location
FROM Location2Table

[vicsaccess]

ok, i love this forum, you guys give me enough information to get me thinking and researching new things i havent tried. so what i ended up with was using a query for each location adding to location code to each table. used the SQL code from each of those to copy and paste into a union query. looks great. thanks for the idea pbaldy.

[pbaldy]

No problem. A UNION query is a common workaround to a non-normalized design.