PC Review


Reply
Thread Tools Rate Thread

Bill of Material Query & Report

 
 
=?Utf-8?B?U2FtYW50aGE=?=
Guest
Posts: n/a
 
      1st Feb 2005
I need to write a query that provides a multi-level Bill of Material using
Access. The table is structure with the following fields:
Assembly(parent)
Component(child)
ItemSequence
QtyPer

I am currently able to write a single level Bill of Material query. I need
to set it up for about 6 levels deep. I know it can be done using a nested
query but I haven't figured it out yet. Can anyone help me with this?
Thanks in advance.
Samantha
 
Reply With Quote
 
 
 
 
Michel Walsh
Guest
Posts: n/a
 
      1st Feb 2005
Hi,


If the number of level is fixed, you can try (here, for a max of 3
levels):


SELECT d.who, [a].[qtyper]*[b].[qtyper]*[c].[qtyper]*[d].[qtyper] AS Expr1
FROM ((ParChi AS a LEFT JOIN ParChi AS b ON a.who = b.parent) LEFT JOIN
ParChi AS c ON b.who = c.parent) LEFT JOIN ParChi AS d ON c.who = d.parent
WHERE (((d.who) Is Not Null));



with


ParChi
who parent qtyper
1 1 1
2 1 4
3 1 5
4 2 3
5 4 1
6 2 3




the result is


Query10
who Expr1
5 12
3 5
2 4
1 1
6 12
4 12



which can be read a we need 1 piece 1, 4 pieces #2, ... 12 pieces #6 ( 3
for each #2 we make, and need 4 pieces #2)




For a variable number of nesting levels, it may be preferable to use a
nested set approach.



Hoping it may help,
Vanderghast, Access MVP



"Samantha" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I need to write a query that provides a multi-level Bill of Material using
> Access. The table is structure with the following fields:
> Assembly(parent)
> Component(child)
> ItemSequence
> QtyPer
>
> I am currently able to write a single level Bill of Material query. I
> need
> to set it up for about 6 levels deep. I know it can be done using a
> nested
> query but I haven't figured it out yet. Can anyone help me with this?
> Thanks in advance.
> Samantha



 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      1st Feb 2005
hi,
i doubt you will do it with 1 query.
i had to do multi-level Bill of Materials up to 4 levels
deep. I had to have a query for each level which i dumped
into a temp table. i had to create a form just for BOMs
because of all the different queries that had to run which
included a delete temp table query which i had to run
before the next BOM could run. i use a select case to test
the parent item id entered in the form and run the
appropreate series of queries based on level on the id
entered.
post back if you need more info/direction.

>-----Original Message-----
>I need to write a query that provides a multi-level Bill

of Material using
>Access. The table is structure with the following fields:
> Assembly(parent)
> Component(child)
> ItemSequence
> QtyPer
>
>I am currently able to write a single level Bill of

Material query. I need
>to set it up for about 6 levels deep. I know it can be

done using a nested
>query but I haven't figured it out yet. Can anyone help

me with this?
>Thanks in advance.
>Samantha
>.
>

 
Reply With Quote
 
=?Utf-8?B?U2FtYW50aGE=?=
Guest
Posts: n/a
 
      9th Mar 2005
Hi,
I tried the self-joining process, which works for up to 3 levels. When I
added the 4th level, it's too complex and keeps crashing the database.

Can you provide me with more detail/info on how you designed yours? And how
long did it take to run your report?

thank you so much! I've spent so much time on this already trying to get it
to work!
-S
"(E-Mail Removed)" wrote:

> hi,
> i doubt you will do it with 1 query.
> i had to do multi-level Bill of Materials up to 4 levels
> deep. I had to have a query for each level which i dumped
> into a temp table. i had to create a form just for BOMs
> because of all the different queries that had to run which
> included a delete temp table query which i had to run
> before the next BOM could run. i use a select case to test
> the parent item id entered in the form and run the
> appropreate series of queries based on level on the id
> entered.
> post back if you need more info/direction.
>
> >-----Original Message-----
> >I need to write a query that provides a multi-level Bill

> of Material using
> >Access. The table is structure with the following fields:
> > Assembly(parent)
> > Component(child)
> > ItemSequence
> > QtyPer
> >
> >I am currently able to write a single level Bill of

> Material query. I need
> >to set it up for about 6 levels deep. I know it can be

> done using a nested
> >query but I haven't figured it out yet. Can anyone help

> me with this?
> >Thanks in advance.
> >Samantha
> >.
> >

>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bill of material type query DKS Microsoft Access Queries 3 8th Oct 2009 11:46 PM
creating an SQL to display cost and material want to type material =?Utf-8?B?YmFicw==?= Microsoft Access Forms 1 5th Sep 2007 06:54 AM
do you have a BOM bill of material word doc? =?Utf-8?B?Qk9NIE5FRURFRCBCSUxMIE9GIE1BVEVSSUFMUyBJ Microsoft Word Document Management 1 19th May 2005 01:46 PM
querying a bill of material table =?Utf-8?B?Sm9obkx1dGU=?= Microsoft Access Queries 2 13th May 2005 04:52 PM
making one material list from mulitple vendor material lists =?Utf-8?B?SW4gdGhlIGJlZ2lubmluZw==?= Microsoft Excel Worksheet Functions 1 8th Jan 2005 03:49 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:34 AM.