Confused - Query with multiple tables

[Guest]

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.

 

[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

 

[Jeff Boyce]

Peter

In addition to Michel's suggestions, is there a chance that you have to link
so many tables as a result of the way your data is structured? You describe
"one main table with a number {?!100?!} of link fields that link to other
tables."

In a well-normalized relational database, it would be VERY unusual to have a
table that holds 100 fields -- not bad, not wrong, just very unlikely to
happen.

If you provide a description of your main table fields, the 'group may be
able to offer alternate suggestions.

Also, you didn't explicitly mention which version of Access you are using,
or if the database is split, or if the data is located on a non-Access
database, or if the tables have had referential integrity established, or if
the tables are indexed, or ...

Knowing these things could help, too...

 

[Guest]

e are building a database to capture the Specifications for Road Transport
Tankers.
There are up to 100 individual features of a Tanker that must be captured,
each feature has multiple options and each option needs one text field and to
memo fields to fully describe. (100 text + 100 text + 200 Memo = 400 fields)
Our first attempt is one table holding the 100 features (Fields) plus one
table for each feature which holds the one text and two memo fields. So we
are looking at up to 101 tables.
The query was to pull this together, it worked fine until we linked the 16th
table in the query, then the system grinds to a halt.
We then tried to create separete queries and link these queries with the
same result.

Any suggestions on how to structure the tables and queries will be much
appreciated.

 

[Duane Hookom]

How about 100 related records for 100 features rather than 100 fields? This
would even allow you to modify the features without having to add or
subtract fields.