Merging information from multiple tables into one

[asperthepatriot]

Hello all,

I am hoping someone out there has the answer to my headache!

I have been tasked with creating a database to handle bookings for a chain of 50 restaurants.

Each restaurant will enter its own data and therefore needs its own form and table so that they can only access and change their own data.

So I have 50 identical tables (with identical columns) and forms, each of which is assigned to a seperate user account, one for each restaurant.

So far, so simple...

The problem is that head office need to create queries and reports from all these restaurants at once.

If they want to look up all the bookings made on 13/10/2008 for instance, a pretty easy query, they have to look through 50 tables to find it out.

What I need to do therefore is to have a master table which will automatically be updated with all the information from all the 50 individual tables. Unfortunately I can't figure out how to do this for the life of me.

The obvious way would be to relate all the 50 tables to a master table, but this wouldn't help in generating queries as you'd still have 50 seperate date fields etc. Plus there would be no common data to relate to the master table. You'd have to make them type the data twice, once in the master table, once in their own table so you could relate them together, which would be a total waste of time.

All 50 tables have identical column headings so what I want is for all their data to be collected in one, automatically.

Is this possible and does this question even make any sense? Any help would be very much appreciated!

 

[maxmangion]

you can do that with a union query

select field1, field2, etc
from tablename
UNION
select field1, field2, etc
from tablesname;

 

[asperthepatriot]

I knew there must be some function to allow this but hours and hours of searching turned up nothing. In future i'll know just to come here!

Thankyou.

 

[maxmangion]

you're welcome

 

[stopher]

Each restaurant will enter its own data and therefore needs its own form and table so that they can only access and change their own data.

So I have 50 identical tables (with identical columns) and forms, each of which is assigned to a seperate user account, one for each restaurant.

Why don't you store all the data in one table? You would add a new column e.g. RestaurantID to identify which which records belong to which restaurant. Then your form would handle security to ensure each restaurant only has access to their own bookings. This would be the normalised approach although I appreciate a network might not facilitate this

Chris