subquery/nested query - do I need to redesign my query or my database?

[K. Crothers]

I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.

I am attempting to find all of the component parts of which a part may
be composed. I have a table of parts and their subparts. The problem
is that each of those subparts may be composed of smaller component
parts. The subpart would then be listed in the Part field linked to
each of its subparts in the Subpart field. I need to build a query
which finds all of the subparts to the 4th degree.

The query I built has had queer results, so I am doing something
wrong. I failed to write an SQL statement that nests the subquery
properly. When using the QueryBuilder and putting the subquery in the
table field I get problems even before trying to link the result of
the first query to another field. It displays the result of the first
query once for each row of the table. When I run the subquery as its
own query the result displays only once as expected.

I have researched this in the Access 2002 bible and over the net
without finding help. Suggestions?

[Steve]

I can help!

If you would like my help, contact me at the email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"K. Crothers" <k_********@yahoo.com> wrote in message
news:88**************************@posting.google.c om...

I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.

I am attempting to find all of the component parts of which a part may
be composed. I have a table of parts and their subparts. The problem
is that each of those subparts may be composed of smaller component
parts. The subpart would then be listed in the Part field linked to
each of its subparts in the Subpart field. I need to build a query
which finds all of the subparts to the 4th degree.

The query I built has had queer results, so I am doing something
wrong. I failed to write an SQL statement that nests the subquery
properly. When using the QueryBuilder and putting the subquery in the
table field I get problems even before trying to link the result of
the first query to another field. It displays the result of the first
query once for each row of the table. When I run the subquery as its
own query the result displays only once as expected.

I have researched this in the Access 2002 bible and over the net
without finding help. Suggestions?

[John Winterbottom]

"K. Crothers" <k_********@yahoo.com> wrote in message
news:88**************************@posting.google.c om...

I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.

I am attempting to find all of the component parts of which a part may
be composed. I have a table of parts and their subparts. The problem
is that each of those subparts may be composed of smaller component
parts. The subpart would then be listed in the Part field linked to
each of its subparts in the Subpart field. I need to build a query
which finds all of the subparts to the 4th degree.

The query I built has had queer results, so I am doing something
wrong. I failed to write an SQL statement that nests the subquery
properly. When using the QueryBuilder and putting the subquery in the
table field I get problems even before trying to link the result of
the first query to another field. It displays the result of the first
query once for each row of the table. When I run the subquery as its
own query the result displays only once as expected.

I have researched this in the Access 2002 bible and over the net
without finding help. Suggestions?

Post some information about your table structure(s) with some sample data
and the output you need and we will help.

[John Winterbottom]

"Steve" <sp**@nospam.spam> wrote in message
news:No*******************@newsread3.news.atl.eart hlink.net...

I can help!

If you would like my help, contact me at the email address below.

hey steve, any reason why you don't want to share your help with the rest of
the group?

[CDB]

Mr K Crothers, I would be cautious about seeking help from someone who is so
desperate for work that he will blatantly flout the rules for this
newsgroup. Questionable ethics.

Clive Bolton
"Steve" <sp**@nospam.spam> wrote in message
news:No*******************@newsread3.news.atl.eart hlink.net...

I can help!

If you would like my help, contact me at the email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"K. Crothers" <k_********@yahoo.com> wrote in message
news:88**************************@posting.google.c om...

I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.

I am attempting to find all of the component parts of which a part may
be composed. I have a table of parts and their subparts. The problem
is that each of those subparts may be composed of smaller component
parts. The subpart would then be listed in the Part field linked to
each of its subparts in the Subpart field. I need to build a query
which finds all of the subparts to the 4th degree.

The query I built has had queer results, so I am doing something
wrong. I failed to write an SQL statement that nests the subquery
properly. When using the QueryBuilder and putting the subquery in the
table field I get problems even before trying to link the result of
the first query to another field. It displays the result of the first
query once for each row of the table. When I run the subquery as its
own query the result displays only once as expected.

I have researched this in the Access 2002 bible and over the net
without finding help. Suggestions?

[K. Crothers]

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2g************@uni-berlin.de>...

"K. Crothers" <k_********@yahoo.com> wrote in message
news:88**************************@posting.google.c om...

I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.

I am attempting to find all of the component parts of which a part may
be composed. I have a table of parts and their subparts. The problem
is that each of those subparts may be composed of smaller component
parts. The subpart would then be listed in the Part field linked to
each of its subparts in the Subpart field. I need to build a query
which finds all of the subparts to the 4th degree.

The query I built has had queer results, so I am doing something
wrong. I failed to write an SQL statement that nests the subquery
properly. When using the QueryBuilder and putting the subquery in the
table field I get problems even before trying to link the result of
the first query to another field. It displays the result of the first
query once for each row of the table. When I run the subquery as its
own query the result displays only once as expected.

I have researched this in the Access 2002 bible and over the net
without finding help. Suggestions?

Post some information about your table structure(s) with some sample data
and the output you need and we will help.

SAMPLE DATA

Child Part Table

PartNumber ChildPart (keyID)
1000 1100
1000 1200
1000 1300
1100 1110
1100 1120
1200 1250
1200 1270
1250 1255
2000 2500
2000 2800
3000 3200
etc.

(ChildParts do not follow this numbering convention.)

Part Table

PartNumber (keyID)
1000
1100
1110
1120
1200
1250
1255
1270
1300
2000
2500
2800
3000
3200
etc.

Query Criterion: '1000'
Query Output:
1100
1200
1300
1110
1120
1250
1270
1255

Because it does not pertain to the primary problem, I have omitted
that ChildParts appear as numbers with a decimal place (XXXX.X) that
need to be rounded off to integers to find their ChildParts.

Thanks for any suggestions!

[John Winterbottom]

"K. Crothers" <k_********@yahoo.com> wrote in message
news:88**************************@posting.google.c om...

I have researched this in the Access 2002 bible and over the net
without finding help. Suggestions?

Post some information about your table structure(s) with some sample data and the output you need and we will help.

SAMPLE DATA

Child Part Table

PartNumber ChildPart (keyID)
1000 1100
1000 1200
1000 1300
1100 1110
1100 1120
1200 1250
1200 1270
1250 1255
2000 2500
2000 2800
3000 3200
etc.

(ChildParts do not follow this numbering convention.)

Part Table

PartNumber (keyID)
1000
1100
1110
1120
1200
1250
1255
1270
1300
2000
2500
2800
3000
3200
etc.

Query Criterion: '1000'
Query Output:
1100
1200
1300
1110
1120
1250
1270
1255

If you know you will only ever have four levels you could do something like
this. It's not pretty but it will work.

parameters [partnum] int;
select '' as hierarchy, PartNumber
from Parts
where PartNumber = [partnum]
union all
select '-', ChildPart
from ChildParts
where PartNumber = [partnum]
union all
select '--', c2.ChildPart
from ChildParts c1
inner join ChildParts c2 on c1.ChildPart = c2.PartNumber
where c1.PartNumber = [partnum]
union all
select '---', c3.ChildPart
from
(
ChildParts as c1
inner join ChildParts as c2 on c1.ChildPart = c2.PartNumber
)
inner join ChildParts AS c3 on c2.ChildPart = c3.PartNumber
where c1.PartNumber=[partnum]
order by hierarchy, PartNumber
There are better designs for hieracrchies - detaills of one at
http://www.mvps.org/access/queries/qry0023.htm

[K. Crothers]

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2g************@uni-berlin.de>...

"K. Crothers" <k_********@yahoo.com> wrote in message
news:88**************************@posting.google.c om...

> I have researched this in the Access 2002 bible and over the net
> without finding help. Suggestions?
Post some information about your table structure(s) with some sample data and the output you need and we will help.

SAMPLE DATA

Child Part Table

PartNumber ChildPart (keyID)
1000 1100
1000 1200
1000 1300
1100 1110
1100 1120
1200 1250
1200 1270
1250 1255
2000 2500
2000 2800
3000 3200
etc.

(ChildParts do not follow this numbering convention.)

Part Table

PartNumber (keyID)
1000
1100
1110
1120
1200
1250
1255
1270
1300
2000
2500
2800
3000
3200
etc.

Query Criterion: '1000'
Query Output:
1100
1200
1300
1110
1120
1250
1270
1255

If you know you will only ever have four levels you could do something like
this. It's not pretty but it will work.

parameters [partnum] int;
select '' as hierarchy, PartNumber
from Parts
where PartNumber = [partnum]
union all
select '-', ChildPart
from ChildParts
where PartNumber = [partnum]
union all
select '--', c2.ChildPart
from ChildParts c1
inner join ChildParts c2 on c1.ChildPart = c2.PartNumber
where c1.PartNumber = [partnum]
union all
select '---', c3.ChildPart
from
(
ChildParts as c1
inner join ChildParts as c2 on c1.ChildPart = c2.PartNumber
)
inner join ChildParts AS c3 on c2.ChildPart = c3.PartNumber
where c1.PartNumber=[partnum]
order by hierarchy, PartNumber
There are better designs for hieracrchies - detaills of one at
http://www.mvps.org/access/queries/qry0023.htm

I will attempt to implement this shortly, but I wanted to write,
"Thank you", immediately. I downloaded the BOM database. The mvps.org
site helped me answer another question as well. Hopefully, I will be
able to contribute at some point.

Thanks again.

Kevin