Need to upate union query statement every month

[SSBHA]

I have 47 union queries that collect data for 47 pumping stations on monthly
basis; then plots each query result. Every month a table is added to the
list of individual union query. I got tired of adding the union select
statement manully to 47 queries and started to write a little module to
handle it. Using Visual Basic scripting, I know how to create a table or a
query in a database; I know how to add records to existing table, etc. But I
don't know how to add a sql satement to an existing union query using visual
basic coding. I have tried variours DoCmd actions and Variou Methods and
none worked. Can you help?

 

[Jeff Boyce]

You might not view this as help...

If your table structure/design is such that "Every month a table is
added...", you probably have ... a spreadsheet!

You won't get very good or easy use of Access' relationally-oriented
features/functions if you insist on feeding it 'sheet data.

If you'll post back an example of what you are using for a table structure,
folks here may be able to offer alternative suggestions.

For example, if you are adding a table to represent a (new) month, a
well-normalized design would use a SINGLE table with an added field to hold
the date. That way, you don't need a new table each month, just new records
that include the month as part of the data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP