Multiple Queries

  • Thread starter Thread starter Daniel
  • Start date Start date
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
 
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?
 
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]
 
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
 
Back
Top