Linking built queries to new tables

[prag]

I have a set of tables and queries built for those tables. Now I'll be recieving another 50 tables that contain similar data. I can't use the append query because the table would be really big and it woudn't be efficent. Is there a way I can put all those tables in lets say five tables in different databases and yet use the same set of queries I have created to get the results? Help will be much appreciated.

[Brian62]

You can create another database and link tables to your current DB.

[dcrake]

Your problem is going to be naming conventions. If your existing queries use a table called TblData in Back End 1 and you have another table called TblData in Back End 2 if you try to link both tables one will remain as TblData but the second on will become TblData1. This means that your original query will only work with TblData.

To get around this problem rename the first table TblData to TblDataA then link the second TblData and Rename it TblDataB and so on.

Next Create a union query called TblData

Select * From TblDataA
Union All
Select * From TblDataB

Etc

This way your queries will always be looking at all of the tables all of the time.

The convention is correct, however whether it works for you is dependant on your tables being the same in structure and data types. What you are actually doing is stacking the data one on top of each other, however the more records they are in each table will reflect on the overall performance.

David

David

[prag]

Ok I understand what you are saying. However, I have elven complex queries based of the that particular table and other queries I have developed using that table as a data source. David Will the process you mentioned above will run all the queries incorporating the new data. And another question is do i have to use the union query as my data source for the query?

[dcrake]

The union query is a simulation of a collection of tables into one big one. As long as yourt naming conventions don't conflict with the originals then you should be all right. You need to go backwards through you queries until you hit the query that is not built on an underlying query but as the source table.

David

[prag]

Ok to make sure that I'm getting you accurately. I am using one query from the source table and all other queries are based off that first query. All I need to do now is change the data source for that first query to the union query that I just built right? I am talking about huge records here so I need to save time that why I need to be really sure what I'm doing.

[dbDamo]

That is correct prag. Change the source of the first query from the table to your union query.