Filling a Container

[DRPJL]

Hello! I need to take particular items, and fill a container to the top,
then show what is contained:
Item 1: 2pc
Item 2: 2pc
Item 3: 3pc
Item 4: 5pc
If a container holds 4pc, how can I break out the items to reflect that 2pc
of Item 1, 2 pc of Item 2 are in Container 1, 3pc of Item 3, 1 pc of Item 4
are in Container 2, and 4pc of Item 4 are in Container 3?
I realize that I may not have explained this very well, please help if you
are able.

 

[Guest]

I agree that maybe you didn't explain this very well. You say you are filling
a container, yet your example filled three. Why were the combinations chosen
the way they were. Why not have 2pc of Item 3 and 2pc of Item 4? I'm not
seeing the logic you want to use.

 

[Guest]

You can do this with three tables. It is fairly common in "Kit" inventory
systems.

First table is to identify a "container", Second table is to identify items,
and Third Table to identify items in a container:

tblContainer
ContainerID - AutoNumber PK
CtrDESCRIPTION - Text
CtrMaxItems - Long Integer Maximum number of items allowed in the container

tblItem
ItemID - Autonumber PK
ItmDESCRIPTION - Text

tblContainerItems
ContainerID - Long Integer FK to tblContainer
ItemID - Long Integer FK to tblItem
ItemCount - Long Integer Number of Items in the Container.

 

[DRPJL]

I was trying to expand upon the container filling with a more realistic
example. The combinations were chosen at random, which is part of my issue.
If I have completely random items/quantities, how do I ask Access to
containerize them?

I agree that maybe you didn't explain this very well. You say you are filling
a container, yet your example filled three. Why were the combinations chosen
the way they were. Why not have 2pc of Item 3 and 2pc of Item 4? I'm not
seeing the logic you want to use.

Hello! I need to take particular items, and fill a container to the top,
then show what is contained:

[quoted text clipped - 7 lines]

I realize that I may not have explained this very well, please help if you
are able.

 

[John Vinson]

I was trying to expand upon the container filling with a more realistic
example. The combinations were chosen at random, which is part of my issue.
If I have completely random items/quantities, how do I ask Access to
containerize them?

This is a famous example of a "NP-Complete" mathematical problem,
called Satisfiability if I remember correctly. NP-Complete means that
(unless some genius comes up with a Field-medal proof against all
odds) there is no polynomial-time solution. As the number of candidate
objects goes up, the number of possible solutions which must be tried
goes up exponentially; finding the "best" solution can be all but
impossible.

About the best you can do is "the greedy algorithm" - select the
largest item group that will fit, put it into the container, and
recurse until the container is full or there are no groups that will
fit. This requires VBA code.

John W. Vinson[MVP]

 

[DRPJL via AccessMonster.com]

Yes, I agree. Can you help w/ the VBA? Even if you can give me direction,
it would be a huge help. Thank you in advance.

 

[John Vinson]

Yes, I agree. Can you help w/ the VBA? Even if you can give me direction,
it would be a huge help. Thank you in advance.

Well, working it all out would be billable, I think - but in a
nutshell, you'll want to have two Recordsets: one for the containers,
one for the items that go into the container. You'ld loop through the
containers, and construct a Query selecting all items that will fit,
sorted in descending order by size. Add the first (largest) item to
the container and rerun the second query with the new size, until the
container is full, there are no more items to pack, or there are no
items that fit; then move to the next container.

To start with you'll need a table of Containers (with fields for
ContainerID and Capacity); Items (ItemID and Size); and Contents with
fields ContainerID and ItemID. Your first recordset would need to be
a Totals query joining all three tables, subtracting the sum of
Items.Size from Containers.Capacity to find the amount of room left.

It's an interesting problem; I'm sure someone has a worked - out
example but I may play with it just to see if I can come up with a
good way to do it!

John W. Vinson[MVP]