Problems Linking Like Tables in Parallel

[xhonzi]

I'm having difficulties with linked like tables. I have an accounting database and each year has its own table. Each table has the same fields, just different data. In a form/report, I want to be able to load as many or as few tables and view all the data in the same area. For example, I don't want to see "1998.funds" and "1999.funds", I want to see "funds" and if that particular record is in table "1999" then so be it.

The only way of getting around this, that I have come across, is to have another "common" table, with all of the same fields, that is blank and that is actually linked to all of the child tables and forms/reports. It's not populated until I run a query (seperate for each year table) that appends the data from a year table to that "common" table. Running a query for each of the years I want to access (no pun intended) at the time and then referring to that common table.

It seems to me there has to be a better and simpler way of doing this. But I feel like a total newbie, so someone please point me in the right direction.

Xhonzi

 

[BNPMike]

I think what normally would happen in relational databases is you have a view (query in Access) that UNIONS whichever table versions you need. (I've heard of tens/hundreds of tables unioning millions of records in corporate databases). You then run your report/form off the view/query. The trick of course is working out which tables you need. I'm guessing here but I suspect Jet builds the union before doing any selects (relational databases tend to just optimise at the single select statement level) so you could get very poor performance.

 

[xhonzi]

Okay I did that (the union query) and so far it's working beautifully. What I miss is the cascading, but I guess that is the price one pays. Also, I guess that means that I can't make a form for INPUT based on that query since it doesn't cascade back. Someone mentioned (I double posted this in Forms and Queries) that I could make a query that would turn the union query into a table. But that wouldn't give me cascadig abilities, would it?

Also, can I write a script (somewhere?) where I could select from a list the tables I wanted to include and it would write that union query for me?

Xhonzi