Gathering data from two identical tables

[PaddyIrishMan]

Hi all,
I have a database which will contain a large number of records. With this in mind I've created an empty copy of the main table. The idea being that when the database gets slow, the Admin will have the option of transferring records which arent current into this table.

The problem is, that I still need data from both tables in my historical reports. With this in mind, I created a query, when the user wants to view a report a new table is created with all of the data from both tables. This works (for one user at a time) & I'd say it'll be very slow when the database gets big.
The problem is the Performance impact & also the fact that if one user is viewing reports then nobody else can as the table is locked & can't be overwritten.

Can anybody offer advice/solution/alternative?
Any help much appreciated,
Regards,
Patrick.

 

[Rich]

I don't understand why you need to create another table when using data from two tables, why not just use subReports?

 

[PaddyIrishMan]

Thanks for the reply Rich,
I'll look into this - hadnt considered using this approach. The reason I've been creating a third table is for querying purposes - to get all of the data into it's relevant fields rather than having two sets of the same fields. Is it common practice to use "Overflow" tables to handle huge numbers of records or is there an alternative?

Regards,
Patrick

 

[Pat Hartman]

It is not necessary to create a third physical table. You can bring the two tables together by using a union query and using that query as the recordsource for your reports. Union queries are NOT updateable and so they are less useful for forms.

Select * from tblA
Union Select * from tblB;

Unions are picky. To use the * syntax, the tables must contain the same number of columns and they must be in the same order. Column name is not relevant.

If you are having trouble with your forms because the table is too large, the problem is most likely because the forms are based directly on the tables or on queries that do not have where clauses.

I would not split the table unless it proves to be absolutely necessary because you'll need to keep unioning it back together again for reporting.

 

[PaddyIrishMan]

Thank you both for your suggestions.
A Union query required less work for me to implement & does the job nicely.

Regards,
Patrick