Here is a solution to a fairly common problem. That of dealing with many tables of identical structure that should be able to use the same query without rewriting the query for each table. Here is a way to get around the need to change the query to access different table.
We may often find ourselves with a database that contains several tables all of the same structure that for one reason or another are not combined into a single table.
I inherited, and then continued, the development of a material estimating system in MS Access, wherein the product to be estimated would be brought into the database as a new table for each new estimate. The table structure would always be the same, so that any queries developed for the purpose of applying costs to the product structure would be identical in structure from product to product, but the product tables would all have different names. This would have required me to re-write the queries and reports to point to each new product table.
The solution I came up with was to create a generic table of the same structure as all the product tables and then develop the queries and reports around that table. As each new product was to be evaluated and reports written, the appropriate product table was copied into the generic table for processing. So, instead of changing the query to access different tables, the tables were simply copied into the generic table for the purpose of using the generic queries and reports.
It is necessary to keep track of the specific table (in my case the specific product) that is in the generic table. To this end, I made extensive use of the description field in the table properties feature of MS Access, the specific product in the table was identified there.
It was discovered that renaming the tables was faster than copying them into the generic table. However, this required the name autocorrect feature in MS Access be disabled.
We may often find ourselves with a database that contains several tables all of the same structure that for one reason or another are not combined into a single table.
I inherited, and then continued, the development of a material estimating system in MS Access, wherein the product to be estimated would be brought into the database as a new table for each new estimate. The table structure would always be the same, so that any queries developed for the purpose of applying costs to the product structure would be identical in structure from product to product, but the product tables would all have different names. This would have required me to re-write the queries and reports to point to each new product table.
The solution I came up with was to create a generic table of the same structure as all the product tables and then develop the queries and reports around that table. As each new product was to be evaluated and reports written, the appropriate product table was copied into the generic table for processing. So, instead of changing the query to access different tables, the tables were simply copied into the generic table for the purpose of using the generic queries and reports.
It is necessary to keep track of the specific table (in my case the specific product) that is in the generic table. To this end, I made extensive use of the description field in the table properties feature of MS Access, the specific product in the table was identified there.
It was discovered that renaming the tables was faster than copying them into the generic table. However, this required the name autocorrect feature in MS Access be disabled.