I need help with recursive queries!

[M Skabialka]

I have been asked to design a database for some manufacturing work.

When a widget is made, a set of specifications must be followed, which has a
Spec Number. This document may reference another Spec which may reference
a third, etc. (We don't write the specs)

I have created a table where Spec1 and Spec2 are the fields, and Spec1 calls
Spec2 as a sub-reference.
So Spec1 may say use a certain type of plastic and adhesive, and then refer
to specs about the properties of the plastic and adhesive, and so on. Some
don't call any others.

Spec1 Spec2
BA001 BA005
BA005 BA001
BA005 N23
BA005 N57
BA027 BA657
CGA-16 CF97
N57 BA001
ZD-A-1 none

In my example I have shown that some of the specs refer to specs which refer
to specs which go back to the beginning spec, which seems to happen a lot,
and is why we want a database to eliminate the confusion.

So we now have a widget to build, and need to report the specs required to
be followed.
Another simple table lists the widget and and the primary level of specs
(Spec1)
How can I list all the specs and all the specs they refer to, and then at
least a third layer.

The catch is, how so I stop showing duplicates like this report example:
Widget 1
Spec1 Spec2 Spec3 Spec4 Spec5 Spec6
BA001 BA005
BA005 BA001
BA001 BA005
BA005 BA001
BA005 N23
BA005 N57
N57 BA001
BA005 N23
BA005 N57
N57 BA001

where BA001 keeps getting repeated, then all its sub-specs get repeated.

I have tried building recursive queries but get myself turned around in
circles and end up with more rather than less! Spec 3,4,5 and 6 should only
list specs not shown previously, like this:
Widget 1
Spec1 Spec2 Spec3 Spec4 Spec5 Spec6
BA001 BA005
BA005 N23
BA005 N57

Any wisdom would be greatly appreciated!
Thanks,
Mich

 

[Allen Browne]

As you found, SQL is not good at this kind of thing.

For leads on different approachs, search for "Bill of Materials". Example:
http://www.mvps.org/access/modules/mdl0027.htm

For suggestions on achieving it just in queries, see:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.devx.com/premier/mgznarch/vbpj/2001/07jul01/sqlpro0107/rein0107/rein-1.asp
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

 

[Doug Munich]

Just as an observation, you are creating circular references when BA001
"depends on" BA005, and BA005 depends on BA001. If you try to trace all the
dependencies here you're in an infinite loop. Perhaps the relationship
between a sub spec and its parent (for whatever reason it needs to be
stored) should be in a different field?

As far as duplicates among the dependent specs, you can have a query
return distinct values only.

Doug