Including all tables into a query

[Thomas Toth]

Hi,

I'm not sure whether this is the right group. If not please point me to
the right one.

I have a database containing a table for each month and each table
contains information on the articles in the stock. I would now like to
make a query which goes across the tables and draws the same number for
every table.

The tables all contain a product for each row and the properties of it
in the columns (eg. type, number on stock, number out, number in,
weight,...). All table come from the same source so they all have the
same design except that a product might be in one table and not in an
other one.

I would now like to make a query across all tables to draw out all
information. For example, I would like to know how many pieces of a
specific product were sold in each month in a single table output. Or
how many products of type 'type1' were there on stock each month. Or
what is the average weight on stock each month.

I can build the query in the design view but then I need to add every
table to the query manually. Is there a way to include all tables from
my database that have a specific name-format, such that all tables
starting with data_* are taken into consideration? I have over 2 dozen
queries and it is very tedious to make sure I haven't forgotten one.

Any help is appreciated.

Thanks,
Tom

 

[Guest]

Pardon me, Thomas, but you have made a classic mistake of database design.
You should not have a table for each month. You can see now why this is a
problem. The correct approach would be to combine all your monthly tables
into one table and add a field to identifiy the month and year to which each
record is associated.
If you need to see the data for a specific period or range of periods, use a
query.