Nested bill of materials

[Aerowolf]

Seeing as how the search system is down for maintenance and I can't check to see if this topic has already come up I'm going to ask about it.

I have the following tables:

tblParts & tblBOM

tblParts contains the fields PartID & Description
tblBOM contains PartID, SubPartID, & Qty

What I want to do is create a report that will show a "nested" bill of materials as follows:

PartID Description
SubPartID Description
SubPartID Description
SubPartID Description
SubPartID Description
SubPartID Description

As you can see any subpartid off the main part could have subparts of its own.

Any suggestions?

Thanks

Edwin

 

[JoanneM]

Do you define any where what subpart comes from which part?

Also in the tblBOM Do you have and ID for the BILL so you will know to which BOM you are referring.

This may help me clarify before sending you down a blind alley.

jo

 

[KenReay]

Hi

Have you seen:

http://www.mvps.org/access/modules/mdl0027.htm

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Aerowolf]

Here is my tblBOM with the data I have in it so far:

PARTID SUBPARTID QTY
D94-008R1 D94-008G13L 1
D94-008R1 D94-008G13R 1
D94-008R1 D94-008G14L 1
D94-008R1 D94-008G14R 1
D94-008R1 D94-008G15 4
D94-008R1 D94-008G16 4
D94-008R1 D94-008G17 2
D94-008R1 D94-008G18 2
D94-008R1 D94-008G19 1
D94-008R1 D94-008G20 1
D94-008R1 B285 2
D94-008R1 B286 1
D94-008R1 B299 2
D94-008R1 B300 1
D94-008R1 D94-008-5 2
D94-008R1 D94-008G5 2
D94-008R1 D94-008G6 4
D94-008R1 D94-008G7 4
D94-008R1 D94-008G8 4
D94-008R1 D94-008-44 2
D94-008R1 D94-008-45 8
D94-008R1 D94-008-46 4
D94-008R1 D94-008-42 2
D94-008R1 D94-008-47 2
D94-008R1 D94-008-49 4
D94-008G13L D94-008-1 1
D94-008G13L D94-008-25 2
D94-008G13L D94-008-26 4
D94-008G13L D94-008-27 1
D94-008G13R D94-008-1 1
D94-008G13R D94-008-25 5
D94-008G13R D94-008-26 2
D94-008G13R D94-008-27 1
D94-008G14L D94-008-4L 1
D94-008G14L D94-008-24 4
D94-008G14L D94-008-37 4
D94-008G14R D94-008-4R 1
D94-008G14R D94-008-24 4
D94-008G14R D94-008-37 4
D94-008G15 D94-008-2 1
D94-008G15 D94-008-21 2
D94-008G15 D94-008-22 1
D94-008G16 D94-008-3 1
D94-008G16 D94-008-22 1
D94-008G16 D94-008-23 2

As you can see the D94-008R1 is the main component right now which has several subparts. Some of those subparts have subparts of their own.

For each part I want the report to see if there are subparts in the table and if there are subparts for those subparts and so on.

Maybe I'm trying to be too simple with my table?

Edwin

 

[Dalain]

From what I understand, you will have to Separtate the Parts List in to more than one Table then join then in a query for your report, because right now I can't tell which one is the Master Part.

For Example
If i print a nested list with the Table as you have there.

PARTID SUBPARTID1 SUBPARTID2
Line1 D94-008R1 D94-008G13L D94-008-1
Line2 D94-008G13L D94-008-1

As you can see Line 2 Says D94-008G13L is a Master Part, But it as already listed as a subpart of D94-008R1 in line 1. Imagine a part with 10 or more subparts. one of the Parts would of been printed 10 or more times in the same report.

When creating your report, you can print the Parts by group. Listing the Master Part First, then have a list of the subparts. Or use a SubReport and enter an IdField Name in the Linked Child/Master Field. to link the Main Report with the SubReport.

Before creating you report it would be good the know what is the maximum number of sub parts a Master Part can have and if you want to print them one under another or as a group under the Master one. as this will deturmine how the table is created. (Every "nested" bill, may be require to be placed in a different Table.)

Pierre

 

[Aerowolf]

Actually I was thinking to have the user input one part# for which a Bill of Materials is required. Based on that, if the part D94-008R1 was entered, here is what I'd like to see:

D94-008R1
D94-008G13L (1)
D94-008-1 (1)
D94-008-25 (2)
D94-008-26 (4)
D94-008-27 (27)
D94-008G13R (1)
D94-008-1 (1)
D94-008-25 (5)
D94-008-26 (2)
D94-008-27 (1)
D94-008-5 (2)
B285 (2)

and so on. The numbers in brackets are quantities.

So essentially after a part# is entered by the user, the report lists all the subparts for that part. Each time a subpart is listed the report needs to check to see if that subpart is made up of other subparts. If it is it needs to indent and list those subparts and so on.

Edwin

 

[KenReay]

Hi

I repeat have you seen the download at:

Hi

Have you seen:

http://www.mvps.org/access/modules/mdl0027.htm

it shows how to do exactly what you are asking Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK