Package-in-a-package problem

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.