Laziness in Access

[Ben Kenobi]

I've got a bunch of similarly functioning tables: Specs for group A, old rates for group A, new rates for group A, and then similar tables for B-D. Is there a lazy way to set up a query that points to a selected group, rather than having to create (and keep synched) four separate queries?

I know I can edit the SQL, but that gets to be a pain fast.

[GadgetGeek]

parametric queries? I've not used them to switch tables, but it might be doable. If it works, it would automatically pop-up a box for you to enter table name into.

[Toll Free]

You could combine the specs for groups a-d in one table, adding a field signfiying "a" "b" "c" or "d". Do the same for old and new rates. This might get you back to one query.

[snafu]

SQL Union queries to combine the A,B,C, and D tables for Specs, then for Old Rates, then for New Rates

i.e.

SELECT "A" as RateGroup, Field1, Field2, ... FROM TableA
UNION SELECT "B", Field1, Field2, ... FROM TableB
UNION SELECT "C", Field1, Field2, ... FROM TableC
UNION SELECT "D", Field1, Field2, ... FROM TableD


Similar code for OldRates and NewRates tables.

Then have any additional queries use the union queries instead of the individual tables. All linking would need to include the RateGroup field. In the where portion of the information queries you can then limit RateGroup to where "like [Enter Rate Group * for All]".

Warning, the new union query will not have indexes and this technique will run extremely slow if the underlying tables are large (in excess of 100K rows).

[Ben Kenobi]

150,000 rows apiece. That's one of the reason I'd like to be able to work on them separately :)

[snafu]

With 150K rows apiece the performance will almost certainly degrade.

Hmmmm? OK, as an alternative.

1. For future identification add a field to each table with the RateGroup in it.
2. Write your informational queries to take from a generic version of the tables.
3. Create a small one-record table with a single field to keep track of which RateGroup you are working with
4. Create a parameter based update query (UPDATE tbl SET CurrentGroup = [Enter Rate Group])
5. Then write a macro to:
Execute the query from 4. above
Rename RateGroupTable(s) to GenericTable(s) based on tbl (fetch info from tbl using DFirst function
Open query (which is based on GenericTable(s))
Rename GenericTable(s) back to RateGroupTable(s) based on tbl

A little convoluted but a one-time solution none the less.