How do I select data from several different tables? 1

[captainquigers]

I have several tables with the same schema containing monthly financial data. I would like to create a form so that users can select which of the tables are queried when requesting data.

The naming convention for the tables is as follows;

200404
200405
200406

The contents of each table look like;

Account Number Cost
0001 £50
0002 £60
0003 £80

As I mentioned above, some users would like aggregated data from a single month. Other users would like to have several of the tables selected (which I can do using a UNION query). How would I create a form with check boxes so the users can choose which table(s) themselves?

I apologise if any of this is unclear, I'v been reading online help documents for the last few hours and my brain hurts!

Cheers,

Captain.

 

[dhookom]

I would recommend all one table but it's your database. You can create a union query:
SELECT 2004 as Yr, 4 as Mth, AccountNumber, Cost
FROM 200404
UNION ALL
SELECT 2004, 5, AccountNumber, Cost
FROM 200405
UNION ALL
SELECT 2004, 6, AccountNumber, Cost
FROM 200406
...etc for all tables...

You can then allow users to select by year, month, or accountnumber.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.