I would like to know if MS Access can handle the following problem and if I
could get a few pointers on how to tackle this situation.

Problem Description:

- I have a table which describes a series of Items (e.g. ItemID (PK),
ItemName)
- I have another table with describes a series of packages (e.g. PackageID
(PK), PackageType).
- Fundamentally a package contains items (and items may be repeated within a
package).
- However, a package can also contain multiple other packages (sub-packages)
along-side other (unpackaged) items.
- There is no limit to how many (sub-)packages can be contained within other
(parent) packages.
- A package must at least contain another package or an item.
- I want to setup a linking table that lists what packages are composed of
what "components", where a component can either be an item or another
package. (e.g. ComponentPlacementID (PK), ComponentID)

e.g.: each entity in the following linking table would consist a "placement"
which describes (which package) (contains "=") (which "component")
* P1= i1
* P1 = i2
* P1 = i2
* P1 = i3
* P2 = i1
* P2 = P1
* P3 = i5
* P3 = P2
* P4 = i5
* P4 = P3

- Each "placement" (assignment of package-to-item or package-to-subpackage )
is not necessarily unique. (e.g. two Item i2 are contained in package p1,
furthermore, a package could contain two sub-packages p2). e.g. a "package"
could be a type of food box, and an "Item" could be at type of fruit, e.g. an
apple.

- The primary key (PK) for each table is set to AutoNumber.

Here is where I am stuck: How to make the ComponentID in the linking table
related to two different types of Primary keys (ItemID or PackageID). The
problem I see is that since Item i1 may have ItemID=1 and Package P1 may have
ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what table
to link to.

What is the best way to handle such a scenario? Do I need to include
another field in the "placement" linking table which tells whether this is a
package or item assignment (Yes/No field). Or, do I need to include separate
fields, one for Item assignments which would be related to ItemID and a
separate field for package assignments which is related to PackageID, and
somehow, be careful to make sure that each entity has either: (an ItemID
specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
and PackageID mutually unique and let this unique ID carry the relationship
information?

The end goal is to be able to querry a given package (say P4), and list its
components (i5, P3) and furthermore list all the fundamental items and
quantity thereof contained in entire package P4 (as if all sub-packages would
be opened and all items would be thrown together into the same box, e.g.
2xi1, 2xi2, 1xi3, 2xi5).

Any suggestion would be greatly appreciated.

Perhaps if you thought of each item or package as a "product"?
Product type would then be Package or item.

If there are any fields unique to package or item these would be held in
separate tables - something like Package decription and item description.

You also have a table that links each item or package to its "parent". There
must be several example databases for manufaturing assemblies you could
peruse.


"alainr" <(E-Mail Removed)> wrote in message
news:8A6F879F-DD85-49FC-818C-(E-Mail Removed)...
>I would like to know if MS Access can handle the following problem and if I
> could get a few pointers on how to tackle this situation.
>
> Problem Description:
>
> - I have a table which describes a series of Items (e.g. ItemID (PK),
> ItemName)
> - I have another table with describes a series of packages (e.g. PackageID
> (PK), PackageType).
> - Fundamentally a package contains items (and items may be repeated within
> a
> package).
> - However, a package can also contain multiple other packages
> (sub-packages)
> along-side other (unpackaged) items.
> - There is no limit to how many (sub-)packages can be contained within
> other
> (parent) packages.
> - A package must at least contain another package or an item.
> - I want to setup a linking table that lists what packages are composed of
> what "components", where a component can either be an item or another
> package. (e.g. ComponentPlacementID (PK), ComponentID)
>
> e.g.: each entity in the following linking table would consist a
> "placement"
> which describes (which package) (contains "=") (which "component")
> * P1= i1
> * P1 = i2
> * P1 = i2
> * P1 = i3
> * P2 = i1
> * P2 = P1
> * P3 = i5
> * P3 = P2
> * P4 = i5
> * P4 = P3
>
> - Each "placement" (assignment of package-to-item or
> package-to-subpackage )
> is not necessarily unique. (e.g. two Item i2 are contained in package p1,
> furthermore, a package could contain two sub-packages p2). e.g. a
> "package"
> could be a type of food box, and an "Item" could be at type of fruit, e.g.
> an
> apple.
>
> - The primary key (PK) for each table is set to AutoNumber.
>
> Here is where I am stuck: How to make the ComponentID in the linking table
> related to two different types of Primary keys (ItemID or PackageID). The
> problem I see is that since Item i1 may have ItemID=1 and Package P1 may
> have
> ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what
> table
> to link to.
>
> What is the best way to handle such a scenario? Do I need to include
> another field in the "placement" linking table which tells whether this is
> a
> package or item assignment (Yes/No field). Or, do I need to include
> separate
> fields, one for Item assignments which would be related to ItemID and a
> separate field for package assignments which is related to PackageID, and
> somehow, be careful to make sure that each entity has either: (an ItemID
> specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
> and PackageID mutually unique and let this unique ID carry the
> relationship
> information?
>
> The end goal is to be able to querry a given package (say P4), and list
> its
> components (i5, P3) and furthermore list all the fundamental items and
> quantity thereof contained in entire package P4 (as if all sub-packages
> would
> be opened and all items would be thrown together into the same box, e.g.
> 2xi1, 2xi2, 1xi3, 2xi5).
>
> Any suggestion would be greatly appreciated.
>

At http://www.mvps.org/access/resources/downloads.htm there is a BOM by Ken
Sheridan

I have not used it myself and only glanced at it but:

It should help you get started - the caveat is that the way he prints limits
to viewing to 9 levels.

I have coded to print unlimited levels but not sure if it is the
'best/correct' way. My use was slightly different hierarchy but it worked
for me.

--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pf...g=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"
"alainr" <(E-Mail Removed)> wrote in message
news:8A6F879F-DD85-49FC-818C-(E-Mail Removed)...
>I would like to know if MS Access can handle the following problem and if I
> could get a few pointers on how to tackle this situation.
>
> Problem Description:
>
> - I have a table which describes a series of Items (e.g. ItemID (PK),
> ItemName)
> - I have another table with describes a series of packages (e.g. PackageID
> (PK), PackageType).
> - Fundamentally a package contains items (and items may be repeated within
> a
> package).
> - However, a package can also contain multiple other packages
> (sub-packages)
> along-side other (unpackaged) items.
> - There is no limit to how many (sub-)packages can be contained within
> other
> (parent) packages.
> - A package must at least contain another package or an item.
> - I want to setup a linking table that lists what packages are composed of
> what "components", where a component can either be an item or another
> package. (e.g. ComponentPlacementID (PK), ComponentID)
>
> e.g.: each entity in the following linking table would consist a
> "placement"
> which describes (which package) (contains "=") (which "component")
> * P1= i1
> * P1 = i2
> * P1 = i2
> * P1 = i3
> * P2 = i1
> * P2 = P1
> * P3 = i5
> * P3 = P2
> * P4 = i5
> * P4 = P3
>
> - Each "placement" (assignment of package-to-item or
> package-to-subpackage )
> is not necessarily unique. (e.g. two Item i2 are contained in package p1,
> furthermore, a package could contain two sub-packages p2). e.g. a
> "package"
> could be a type of food box, and an "Item" could be at type of fruit, e.g.
> an
> apple.
>
> - The primary key (PK) for each table is set to AutoNumber.
>
> Here is where I am stuck: How to make the ComponentID in the linking table
> related to two different types of Primary keys (ItemID or PackageID). The
> problem I see is that since Item i1 may have ItemID=1 and Package P1 may
> have
> ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what
> table
> to link to.
>
> What is the best way to handle such a scenario? Do I need to include
> another field in the "placement" linking table which tells whether this is
> a
> package or item assignment (Yes/No field). Or, do I need to include
> separate
> fields, one for Item assignments which would be related to ItemID and a
> separate field for package assignments which is related to PackageID, and
> somehow, be careful to make sure that each entity has either: (an ItemID
> specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
> and PackageID mutually unique and let this unique ID carry the
> relationship
> information?
>
> The end goal is to be able to querry a given package (say P4), and list
> its
> components (i5, P3) and furthermore list all the fundamental items and
> quantity thereof contained in entire package P4 (as if all sub-packages
> would
> be opened and all items would be thrown together into the same box, e.g.
> 2xi1, 2xi2, 1xi3, 2xi5).
>
> Any suggestion would be greatly appreciated.
>

I haven't found the BOM examples by Ken Sheridan on the page you suggested.
However, on that page I found "Bill of Materials Processor" by Robin
Stoddard-Stone, and "A SQL approach to Bill Of Materials" by Michel Walsh
which were most useful. I also found some references to some of Joe Celko's
work on the theory and practice of Nested Sets which provided excellent
insight in the problem and presented ways to deal with trees and hierarchies
with SQL databases.

Many Thanks for these pointers,

Alain

"Craig Hornish" wrote:
> At http://www.mvps.org/access/resources/downloads.htm there is a BOM by Ken
> Sheridan
>
> I have not used it myself and only glanced at it but:
>
> It should help you get started - the caveat is that the way he prints limits
> to viewing to 9 levels.
>
> I have coded to print unlimited levels but not sure if it is the
> 'best/correct' way. My use was slightly different hierarchy but it worked
> for me.
>
> --
> Craig Hornish
>
> Visit weekly Access conferences
> Tuesday, 11:00am-12:30pm (Eastern US)
> Thursday, 3:30pm- 5:00pm (Eastern US)
>
> http://community.compuserve.com/n/pf...g=ws-msdevapps
>
> "Think outside the box, because anything is possible."
> "How long it will take or whether it requires divine intervention is another
> issue"
> "alainr" <(E-Mail Removed)> wrote in message
> news:8A6F879F-DD85-49FC-818C-(E-Mail Removed)...
> >I would like to know if MS Access can handle the following problem and if I
> > could get a few pointers on how to tackle this situation.
> >
> > Problem Description:
> >
> > - I have a table which describes a series of Items (e.g. ItemID (PK),
> > ItemName)
> > - I have another table with describes a series of packages (e.g. PackageID
> > (PK), PackageType).
> > - Fundamentally a package contains items (and items may be repeated within
> > a
> > package).
> > - However, a package can also contain multiple other packages
> > (sub-packages)
> > along-side other (unpackaged) items.
> > - There is no limit to how many (sub-)packages can be contained within
> > other
> > (parent) packages.
> > - A package must at least contain another package or an item.
> > - I want to setup a linking table that lists what packages are composed of
> > what "components", where a component can either be an item or another
> > package. (e.g. ComponentPlacementID (PK), ComponentID)
> >
> > e.g.: each entity in the following linking table would consist a
> > "placement"
> > which describes (which package) (contains "=") (which "component")
> > * P1= i1
> > * P1 = i2
> > * P1 = i2
> > * P1 = i3
> > * P2 = i1
> > * P2 = P1
> > * P3 = i5
> > * P3 = P2
> > * P4 = i5
> > * P4 = P3
> >
> > - Each "placement" (assignment of package-to-item or
> > package-to-subpackage )
> > is not necessarily unique. (e.g. two Item i2 are contained in package p1,
> > furthermore, a package could contain two sub-packages p2). e.g. a
> > "package"
> > could be a type of food box, and an "Item" could be at type of fruit, e.g.
> > an
> > apple.
> >
> > - The primary key (PK) for each table is set to AutoNumber.
> >
> > Here is where I am stuck: How to make the ComponentID in the linking table
> > related to two different types of Primary keys (ItemID or PackageID). The
> > problem I see is that since Item i1 may have ItemID=1 and Package P1 may
> > have
> > ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what
> > table
> > to link to.
> >
> > What is the best way to handle such a scenario? Do I need to include
> > another field in the "placement" linking table which tells whether this is
> > a
> > package or item assignment (Yes/No field). Or, do I need to include
> > separate
> > fields, one for Item assignments which would be related to ItemID and a
> > separate field for package assignments which is related to PackageID, and
> > somehow, be careful to make sure that each entity has either: (an ItemID
> > specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
> > and PackageID mutually unique and let this unique ID carry the
> > relationship
> > information?
> >
> > The end goal is to be able to querry a given package (say P4), and list
> > its
> > components (i5, P3) and furthermore list all the fundamental items and
> > quantity thereof contained in entire package P4 (as if all sub-packages
> > would
> > be opened and all items would be thrown together into the same box, e.g.
> > 2xi1, 2xi2, 1xi3, 2xi5).
> >
> > Any suggestion would be greatly appreciated.
> >
>
>
>

Indeed, I have found an excellent example illustrating the BOM for a car
assembly which seems to present some useful techniques to address my problem.
(see my Reply to Craig in this thread)

Many Thanks for your pointers,

Alain

"David Cox" wrote:

> Perhaps if you thought of each item or package as a "product"?
> Product type would then be Package or item.
>
> If there are any fields unique to package or item these would be held in
> separate tables - something like Package decription and item description.
>
> You also have a table that links each item or package to its "parent". There
> must be several example databases for manufaturing assemblies you could
> peruse.
>
>
> "alainr" <(E-Mail Removed)> wrote in message
> news:8A6F879F-DD85-49FC-818C-(E-Mail Removed)...
> >I would like to know if MS Access can handle the following problem and if I
> > could get a few pointers on how to tackle this situation.
> >
> > Problem Description:
> >
> > - I have a table which describes a series of Items (e.g. ItemID (PK),
> > ItemName)
> > - I have another table with describes a series of packages (e.g. PackageID
> > (PK), PackageType).
> > - Fundamentally a package contains items (and items may be repeated within
> > a
> > package).
> > - However, a package can also contain multiple other packages
> > (sub-packages)
> > along-side other (unpackaged) items.
> > - There is no limit to how many (sub-)packages can be contained within
> > other
> > (parent) packages.
> > - A package must at least contain another package or an item.
> > - I want to setup a linking table that lists what packages are composed of
> > what "components", where a component can either be an item or another
> > package. (e.g. ComponentPlacementID (PK), ComponentID)
> >
> > e.g.: each entity in the following linking table would consist a
> > "placement"
> > which describes (which package) (contains "=") (which "component")
> > * P1= i1
> > * P1 = i2
> > * P1 = i2
> > * P1 = i3
> > * P2 = i1
> > * P2 = P1
> > * P3 = i5
> > * P3 = P2
> > * P4 = i5
> > * P4 = P3
> >
> > - Each "placement" (assignment of package-to-item or
> > package-to-subpackage )
> > is not necessarily unique. (e.g. two Item i2 are contained in package p1,
> > furthermore, a package could contain two sub-packages p2). e.g. a
> > "package"
> > could be a type of food box, and an "Item" could be at type of fruit, e.g.
> > an
> > apple.
> >
> > - The primary key (PK) for each table is set to AutoNumber.
> >
> > Here is where I am stuck: How to make the ComponentID in the linking table
> > related to two different types of Primary keys (ItemID or PackageID). The
> > problem I see is that since Item i1 may have ItemID=1 and Package P1 may
> > have
> > ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what
> > table
> > to link to.
> >
> > What is the best way to handle such a scenario? Do I need to include
> > another field in the "placement" linking table which tells whether this is
> > a
> > package or item assignment (Yes/No field). Or, do I need to include
> > separate
> > fields, one for Item assignments which would be related to ItemID and a
> > separate field for package assignments which is related to PackageID, and
> > somehow, be careful to make sure that each entity has either: (an ItemID
> > specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
> > and PackageID mutually unique and let this unique ID carry the
> > relationship
> > information?
> >
> > The end goal is to be able to querry a given package (say P4), and list
> > its
> > components (i5, P3) and furthermore list all the fundamental items and
> > quantity thereof contained in entire package P4 (as if all sub-packages
> > would
> > be opened and all items would be thrown together into the same box, e.g.
> > 2xi1, 2xi2, 1xi3, 2xi5).
> >
> > Any suggestion would be greatly appreciated.
> >
>
>
>

That was actually the one, I was reading a readme file and it had his name -
don't know what happend.

But that is the one, glad it could help.

--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pf...g=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"
"alainr" <(E-Mail Removed)> wrote in message
news:6F83BE57-73E6-4217-AC45-(E-Mail Removed)...
>I haven't found the BOM examples by Ken Sheridan on the page you suggested.
> However, on that page I found "Bill of Materials Processor" by Robin
> Stoddard-Stone, and "A SQL approach to Bill Of Materials" by Michel Walsh
> which were most useful. I also found some references to some of Joe
> Celko's
> work on the theory and practice of Nested Sets which provided excellent
> insight in the problem and presented ways to deal with trees and
> hierarchies
> with SQL databases.
>
> Many Thanks for these pointers,
>
> Alain
>
> "Craig Hornish" wrote:
>> At http://www.mvps.org/access/resources/downloads.htm there is a BOM by
>> Ken
>> Sheridan
>>
>> I have not used it myself and only glanced at it but:
>>
>> It should help you get started - the caveat is that the way he prints
>> limits
>> to viewing to 9 levels.
>>
>> I have coded to print unlimited levels but not sure if it is the
>> 'best/correct' way. My use was slightly different hierarchy but it
>> worked
>> for me.
>>
>> --
>> Craig Hornish
>>
>> Visit weekly Access conferences
>> Tuesday, 11:00am-12:30pm (Eastern US)
>> Thursday, 3:30pm- 5:00pm (Eastern US)
>>
>> http://community.compuserve.com/n/pf...g=ws-msdevapps
>>
>> "Think outside the box, because anything is possible."
>> "How long it will take or whether it requires divine intervention is
>> another
>> issue"
>> "alainr" <(E-Mail Removed)> wrote in message
>> news:8A6F879F-DD85-49FC-818C-(E-Mail Removed)...
>> >I would like to know if MS Access can handle the following problem and
>> >if I
>> > could get a few pointers on how to tackle this situation.
>> >
>> > Problem Description:
>> >
>> > - I have a table which describes a series of Items (e.g. ItemID (PK),
>> > ItemName)
>> > - I have another table with describes a series of packages (e.g.
>> > PackageID
>> > (PK), PackageType).
>> > - Fundamentally a package contains items (and items may be repeated
>> > within
>> > a
>> > package).
>> > - However, a package can also contain multiple other packages
>> > (sub-packages)
>> > along-side other (unpackaged) items.
>> > - There is no limit to how many (sub-)packages can be contained within
>> > other
>> > (parent) packages.
>> > - A package must at least contain another package or an item.
>> > - I want to setup a linking table that lists what packages are composed
>> > of
>> > what "components", where a component can either be an item or another
>> > package. (e.g. ComponentPlacementID (PK), ComponentID)
>> >
>> > e.g.: each entity in the following linking table would consist a
>> > "placement"
>> > which describes (which package) (contains "=") (which "component")
>> > * P1= i1
>> > * P1 = i2
>> > * P1 = i2
>> > * P1 = i3
>> > * P2 = i1
>> > * P2 = P1
>> > * P3 = i5
>> > * P3 = P2
>> > * P4 = i5
>> > * P4 = P3
>> >
>> > - Each "placement" (assignment of package-to-item or
>> > package-to-subpackage )
>> > is not necessarily unique. (e.g. two Item i2 are contained in package
>> > p1,
>> > furthermore, a package could contain two sub-packages p2). e.g. a
>> > "package"
>> > could be a type of food box, and an "Item" could be at type of fruit,
>> > e.g.
>> > an
>> > apple.
>> >
>> > - The primary key (PK) for each table is set to AutoNumber.
>> >
>> > Here is where I am stuck: How to make the ComponentID in the linking
>> > table
>> > related to two different types of Primary keys (ItemID or PackageID).
>> > The
>> > problem I see is that since Item i1 may have ItemID=1 and Package P1
>> > may
>> > have
>> > ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what
>> > table
>> > to link to.
>> >
>> > What is the best way to handle such a scenario? Do I need to include
>> > another field in the "placement" linking table which tells whether this
>> > is
>> > a
>> > package or item assignment (Yes/No field). Or, do I need to include
>> > separate
>> > fields, one for Item assignments which would be related to ItemID and
>> > a
>> > separate field for package assignments which is related to PackageID,
>> > and
>> > somehow, be careful to make sure that each entity has either: (an
>> > ItemID
>> > specified XOR a PackageID specfied)? Or, should I somehow make the
>> > ItemID
>> > and PackageID mutually unique and let this unique ID carry the
>> > relationship
>> > information?
>> >
>> > The end goal is to be able to querry a given package (say P4), and list
>> > its
>> > components (i5, P3) and furthermore list all the fundamental items and
>> > quantity thereof contained in entire package P4 (as if all sub-packages
>> > would
>> > be opened and all items would be thrown together into the same box,
>> > e.g.
>> > 2xi1, 2xi2, 1xi3, 2xi5).
>> >
>> > Any suggestion would be greatly appreciated.
>> >
>>
>>
>>