Query too complex

[Guest]

I have a series of queries that I'm using to build a compostite material form
other materials and other composites.

The basic structure of the query I'm having problem with "Query too complex"
is

table1 [ Materials]
table2 [Equipment]

These tables are used into another table called [structure builder] which
has look up fields for material1 material2 material3 ...material11 &
equipment1 equipment2...equipment6

I have this this table in query call [structure builder query] in which I
have joined the again the tables:

material1 material2 material3 ...material11 & equipment1
equipment2...equipment6
to each of the corrisponding fields in the [structure builder] table. This
allows me to get material properties into the query. No problem with this
query

The next step is have a second iteration to this use the [structure bulder
query] to build the next composite material.

I do this as simular to the previous material [structure builder query]

First I have a table call [Composite structure builder] that looks up to the
[structure builder query] which I have 8 of these material fields looking up
to this same query.
No problems yet.

I then have a query that uses the [Composite structure builder] as a
'backbone" where I have the [Structure builder query] joined at each of these
fields so I have..

[Structure builder query] ,[Structure builder query_1] ,[Structure builder
query_2] ...[Structure builder query_8] joined to [Composite structure
builder] table. This seams too much for the query and I gett the error "Query
too complex"

I have taken it down to one use of [Structure builder query] join to the
first material and this works, the prblem occurs when join the next
[Structure builder query_1] next material.

Is there a better approach? What am doing wrong?

 

[Guest]

Your structure builder table is not normalized properly. It's pretty evident
by fields named material1, material2, material3, ...material11 & equipment1,
equipment2, ...equipment6

With such a design you will always have trouble getting it to work. I
highly suggest getting some relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceding any further on this
database.

 

[Guest]

Thanks for the suggeston and the note on normalization. I read up on this at
the following site:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

And I do think that I am using first and second normal form as described at
this site. I may not have described what I trying to do properly so again.

I'm in the business of making composite material and each material would go
into making a layer in the final material (materials would be like authors
in the example at this site) So I have a table of only materials ( with
properties such as density, cost , etc) The stage 1 composite material then
has upto 11 layers so I have an ID number in those eleven layers that is a
reference to a record in the materials table. so the final stage 1 structure
could be alum/adhesive/alum/polyeth/EMA/Paper/adhesive/.../...

This then becomes a material that can be used in stage 2 composite

I will need to know what is the composite cost of material, what equipment
will be used to manufacture, what will the physical proporties be of the
composite, etc?

I haven't seen a good example of what I'm trying to do and even at the site
here the example of a book having 12 authors you have a table of authors but
your book query or table would still need 12 fields for author 1, author 2,
etc. if you want it on one record so you can input from a form - am I still
missing something?

--
David McKnight

Your structure builder table is not normalized properly. It's pretty evident
by fields named material1, material2, material3, ...material11 & equipment1,
equipment2, ...equipment6

With such a design you will always have trouble getting it to work. I
highly suggest getting some relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceding any further on this
database.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a series of queries that I'm using to build a compostite material form
other materials and other composites.

The basic structure of the query I'm having problem with "Query too complex"
is

table1 [ Materials]
table2 [Equipment]

These tables are used into another table called [structure builder] which
has look up fields for material1 material2 material3 ...material11 &
equipment1 equipment2...equipment6

I have this this table in query call [structure builder query] in which I
have joined the again the tables:

material1 material2 material3 ...material11 & equipment1
equipment2...equipment6
to each of the corrisponding fields in the [structure builder] table. This
allows me to get material properties into the query. No problem with this
query

The next step is have a second iteration to this use the [structure bulder
query] to build the next composite material.

I do this as simular to the previous material [structure builder query]

First I have a table call [Composite structure builder] that looks up to the
[structure builder query] which I have 8 of these material fields looking up
to this same query.
No problems yet.

I then have a query that uses the [Composite structure builder] as a
'backbone" where I have the [Structure builder query] joined at each of these
fields so I have..

[Structure builder query] ,[Structure builder query_1] ,[Structure builder
query_2] ...[Structure builder query_8] joined to [Composite structure
builder] table. This seams too much for the query and I gett the error "Query
too complex"

I have taken it down to one use of [Structure builder query] join to the
first material and this works, the prblem occurs when join the next
[Structure builder query_1] next material.

Is there a better approach? What am doing wrong?