Multiple Queries

D

Daniel

Hi,

Is there a way in code to do the same query several times
looping it so it goes through say 5 or 6 tables,
selecting the same fields from each with same criteria,
and appending it to the same table. It would save a lot
of time if I could do this rather than running a query
manually and creating all the links.

Cheers

daniel
 
L

Lynn Trapp

Daniel,
While you might have similar queries, it's not possible to have THE SAME
query with 5 or 6 tables. Also, that sounds like a really bizarre database
design. If you have the same fields in different tables, then why not have
all the data in one table?
 
D

Daniel

We get a new table each financial year, if you need to
extract data across a time period incorparating a bit
from one year and a bit from another, you have to do the
SAME query but changing the table, the field names stay
the same but they might be in differant places in the
table depending on guidelines laid down nationally by the
govt department responsible...Does anybody have any
constructive ideas, infortunately I can't change the
designs of the database and I have to work within the
restictions imposed. If it helps each table follows the
same naming convention e.g. xxxx & [Financial year]
 
J

John Vinson

We get a new table each financial year, if you need to
extract data across a time period incorparating a bit
from one year and a bit from another, you have to do the
SAME query but changing the table, the field names stay
the same but they might be in differant places in the
table depending on guidelines laid down nationally by the
govt department responsible...Does anybody have any
constructive ideas, infortunately I can't change the
designs of the database and I have to work within the
restictions imposed. If it helps each table follows the
same naming convention e.g. xxxx & [Financial year]
Click to expand...

Two suggestions:

- Write VBA code to construct the query using the table name. You
cannot use a Parameter Query for this purpose unfortunately.

- Or, create a properly normalized table in your own computer (in a
separate backend from the government-authorized database) and run
Append queries to move the annual non-normalized data into it. Your
queries will then become simpler by far and you will not have changed
the misdesigned database.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top