I have 50 tables or so and I was wondering the best way I could avoid having to make 50 querries that are all the same to pull the information out that I need. I was think that maybe is would be better to consolidate all the tables into one large "mastertable" then I would only have to creat 1 query to pull the information out that I needed. If anybody can give me some general advice I would really appreciate it.
using a single query for multiple tables
- Thread starter Ragalots
- Start date
I would vote against a "Master Table". However THAT depends on the data and how it relates between the 50 tables you have.
Is the information you are seeking based off of a "set" criteria that is gathered from the user input?
Maybe giving us an example of a few of the tables and the information you are seeking would clarify things a bit.
Is the information you are seeking based off of a "set" criteria that is gathered from the user input?
Maybe giving us an example of a few of the tables and the information you are seeking would clarify things a bit.
- Local time
- Today, 18:37
- Joined
- Feb 19, 2002
- Messages
- 47,191
If the 50 tables share an identical format, they should be combined into a single table. You can add an additional column to the table to hold what ever information would identify which of the 50 "types" any particular row belongs to.
Thanks for taking the time to respond to my posts guys. Sorry for the delay in getting back to you, my Wife and I had a baby boy! I should have posted my question as follows when running a query is there a way that you can specify what table you want the query to actually pull data from, rather than editing the design of the query itself. I'm thinking this will require some VB code. This is what I'm really looking for.
Thanks, once again sorry for the delay in getting back to your response. Having a hard time thinking about work while I am thinking about my little boy!
Each table represents an individual company and all of their insurance claims over an extended period of time. Five to Fifteen years usually. The columns in each table consist of things like policy number, coverage, occurrence number, about 20 columns in all(all insurance related). Every table is exactly identical in terms of table structure. The reason this database has so many tables is that every re-insurance company that we deal with sends us this report for a specific company that we request. I am running a simple query that shows me if and how many times a specific policy had multiple claims so that we can investigate. I get updates on most of these tables monthly and I was hoping to figure out a way that I can just click on a button on a form and have it ask me what table I would like to run this simple query on. Right now I am changing the source of the query manually by entering design mode inside the query itself. Thanks again for your input.
Each table represents an individual company and all of their insurance claims over an extended period of time. Five to Fifteen years usually. The columns in each table consist of things like policy number, coverage, occurrence number, about 20 columns in all(all insurance related). Every table is exactly identical in terms of table structure. The reason this database has so many tables is that every re-insurance company that we deal with sends us this report for a specific company that we request. I am running a simple query that shows me if and how many times a specific policy had multiple claims so that we can investigate. I get updates on most of these tables monthly and I was hoping to figure out a way that I can just click on a button on a form and have it ask me what table I would like to run this simple query on. Right now I am changing the source of the query manually by entering design mode inside the query itself. Thanks again for your input.
pcs
Registered User.
- Local time
- Today, 17:37
- Joined
- May 19, 2001
- Messages
- 398
think i would consolidate the tables into a master table as Pat suggests, you would need to add one field to the master table: CompanyNumber.
don't think there is any way change the table source of a stored query. you could, of course, generate SQL code in VBA to construct your query. but, i see no reason to to take this approach, as you would have to present a selection list of possible tables, etc....
i would keep it 'clean and easy' and just make a master table...
so are you getting any sleep?
hth,
al
don't think there is any way change the table source of a stored query. you could, of course, generate SQL code in VBA to construct your query. but, i see no reason to to take this approach, as you would have to present a selection list of possible tables, etc....
i would keep it 'clean and easy' and just make a master table...
so are you getting any sleep?
hth,
al
