Confused - Query with multiple tables

P

Peter

I have created a query that uses one main table with a number of link fields
that link to other tables. The query worked fine until I linked the 15th
table then the query slowed down, after I linked the 16th the query sent my
system resources to 100% and locked up the PC.
Is there a limit on the number of linked tables in a query, I need to link
at least 100 tables to get the job done.
I am now considering dumping all of the data in one table with duplicated
data across records, it seems to me that this is a backward step, but I don't
know what elses to do.
Any help will be greatly appreciated.
 
M

Michel Walsh

Hi,


If you use a cross join, the number of records increases as the
MULTIPLICATION of the number of records of each table. I suggest you use
INNER JOIN, to restrict as much as possible as SOON AS POSSIBLE the number
of generated records. To generate one million of records, that may takes a
couple of minutes, and you may think your computer is locked, while it is
just busy to produce scrap. Rumors are that some queries can have taken up
to 17 hours to be solved! The computer just does what you ask.


Under Access Specifications, we have a maximum of 32 tables allowed, in
a query, but we can nest queries 50 times. See help file under "Access
specifications", then Query.


Hoping it may help,
Vanderghast, Access MVP
 
Top