Need to combine linked tables into one table that will auto update

[ldavis]

I have linked tables in my database for each month of the year with the exact same fields (ex. Nov2010, Dec2010, Jan2011, etc.). What I need to do is combine all the data into 1 table or query but I need that combined table to automatically update when the linked tables update. Is there a way to do this? I already know how to use the append query but this doesn't work for me because it does not update when data in the individual tables change.

[rpeare]

I'm not quite following your question. Could you give a simple example of your tables and what you want your result to be, it seems like you want to be able to enter a new record on TableA and have it automatically look up a value from TableB but I'm getting lost in your description a little.

[ldavis]

rpeare,

Thank you so much for the fast reply.

Here is my table layout:

Linked Tables:
Oct2010
Nov2010
Dec2010
Jan2011
Feb2011

Other Tables:
AllData (this table will combine data from all the tables)

The data in the linked tables above is constantly changing. I need the AllData table to constantly update based on those changed from the monthly tables.

[rpeare]

whoa, that's very bad design if each month of activity has it's own table. Do you have any control over the design of this database?

Instead of having

Table: Oct2010 with relevant fields

why not have

Table:Activity
Month >other exiting fields in each current table
Oct2010

It's going to be impossible for you to write a static query that will combine all your tables into one query. You will have to continually modify your query or have a form that helps you build a SQL statement based on which month tables you include in your query.

Let me restate, what you've said you have is a very poor choice for table design and if you have any control over it at all I would strongly suggest you change your database to a more normalized structure.

What you're asking to do can be done with a UNION query

SELECT * FROM <tablename1>
UNION ALL
SELECT * FROM <tablename2>
UNION ALL
SELECT * FROM <tablename3>
..etc

will work as long as the table design is identical across all the tables.

[ldavis]

I was afraid of that. My client needs the data by month in excel which is why I have the database structured that way. Thank you so much for your help.

[rpeare]

You can give your client the data in any format they want as long as you're collecting the information. You can convert to the structure I mentioned and still be able to give your clients a month by month accounting, you would just change your exporting procedure to export a query (which limits the data to the stuff they want to see) as opposed to exporting an entire table.

[ldavis]

I have been considering a re-structure. So what you suggest is to have all the data in one table instead of by month right?

[rpeare]

Exactly right. The only thing you would have to do to your table structure is to add one more field. Then for all the information you append to the new 'combined' table is to add the MONTH of the data you're adding which you could mimic by using your current table naming convention.