Stock Breakdown Database

[vicky666]

Hey All,

I'm hoping this won't be too difficult, but I'll see how it goes. This database will be a list of drawing numbers. Now each drawing number can have many other drawing numbers linked to it. Basically a drawing number has many other items of stock within a drawing, which also have their own drawing numbers.

So I'm thinking I need a table that almost links back on itself.

When I began to design this I thought that a simple relationship structure would be fine with...

TblCompilations ---< TblCLS/CPS >--- TblComponents

But some of the compilations need to link to other compilations, so this doesn't work.

I was recommended to try the following...

-TblComponents-       -TblLink-
ComponentID -----< ComponentID
    "       -----< ComponentID2

But I couldn't get this working either, as the queries wouldn't extract any data.

If anyone could think of anything that would make this possible please help

Cheers

Vicky

 

[SkipVought]

Vickey,

Sounds like a multi-level bill of material.

Parent Part A has Components B & C.

Part B, however is ALSO a Parent, having its own components C & D.

Parts C & D, in this example are purchased complete, having no components listed and therefore never being listed as Parent Parts.

Is this the situation?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vicky666]

Hey SkipVought,

Sorry about the late reply, I thought I had already reponded to this but shortly after the server went down, must have messed it up, so here it is again

A multi-level bill of materials is exactly the kind of thing I need. Would you be able to help me in designing it, or could you recommend a web site that might?

Thank you for your help

Vicky

 

[vicky666]

OK, well I've searched far and wide and I found this...

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

hope it helps anyone else with this problem.

Cheers

Vicky

 

[SkipVought]

Vicky,

My only recommendataion is to think in terms of differentiating between Part-related data (which describes individual parts) and BOM-related data (which describes parent-child relationships).

If your BOMs are affected by engineering changes, you need to consider configuration management break points, depending on how you discriminate between pre-change and post-change part designation. In the aircraft manufacturing industry, effectivity contol is important. In some other inductry, it may not matter.

Often, the BOM is STORED in a relational or hierarchical database but then PROCESSED in some sort of FLAT top-down indentured file or table -- processed in terms of merging the BOM with SCHEDULE, WORK CENTER CAPACITY, LABOR CAPACITY etc in order to generate work orders and other shop floor reports.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884