Inventory Assembly/Sub-component question

[Bob A.]

I'm going to use a McDonald's analogy of my problem because it's familiar. I
can easily inventory the comings and goings of my inventory items. I make a
Big Mac and it is added to the inventory total. I sell a Big Mac and it is
deleted from the inventory total. I can easily enough inventory the comings
and goings of Big Mac meals too (exactly the same way as anything else).
What I can't see how to do is correctly handle the sale of a Big Mac Meal.
How do I automatically deduct one Big Mac, one fries and one drink (and the
Meal) every time I sell a Big Mac Meal? By the way, I am working in Access
97 with a database created by the Database Wizard for Inventory Control.

 

[George Nicholson]

One approach: Use a translation table between orders and inventory.

Table-InventoryItems (and Cost):
Contains single physical objects that *could* be ordered, NOT meals.
Big Mac
SmlFry
MedFry
12ozDrink
16ozDrink
6pkNugget
12pkNugget
etc.

Table-MenuItemsXref:
MenuItem = x # of InventoryItems (i.e., the "recipe" for the MenuItem
in terms of InvItems)
BigMac MenuItem = 1 BigMac InvItem (most entries will be as simple as
this)
BigMacMeal MenuItem = 1 BigMac InvItem
" " + 1 MedFryInvItem
" " + 1 12ozDrinkInvItem
Fry2pack MenuItem = 2 MedFry InvItem (special promotion)
etc

Table-MenuItems (and Price)
Big Mac
SmlFry
MedFry
BigMacMeal
Fry2pack (special promotion)
etc.
..
For each MenuItem ordered, you use MenuItemXref to find the associated
Inventory Items and their quantities. Special sales promotions? Bring 'em
on, they're a snap.

Hope this helps,

 

[Bob A.]

Do you have another approach to consider?

The wizard has set up a database with an Inventory Transactions table. The
inventory count is determined by transactions. I have wondered if I could
set up a table (probably without relationships to other tables) that I could
use with an SQL append query and some VBA to deal with the Inventory
Transactions table when a "meal" is sold. That (I'm nuts - HA) sounds
complicated. I don't know if I can pull-off that scheme.

Bob Anderson
Here's a link to the Wizard created file, if you need it to help:
http://planetkc.com/rga4one/access/InvCntrlWiz.zip

 

[George Nicholson]

Bob:

Sorry, that's the only way i can really think to approach it. There may
well be another way, but my mind set can't grasp any at the moment.

I think you need 2 additional tables that would sit above what the wizard
creates: An "Items for Sale" table (which would include both individual
Products being sold as a single item and meals or gift baskets consisting of
multiple Products represented by a single ItemForSale) and a "linking" table
that, for each ItemForSale, would specify the ProductID (and quantity)
associated with that ItemForSale. The linking table would have a
one-to-many relationship with the ItemsForSale table on one side and another
one-to-many with the Products table on the other.

When there is a sale, the ItemForSale information is passed through the
linking table to determine what Products are involved and what their
Inventory Transactions should be.

The db created by this wizard doesn't record sales. It does provide a means
of manually entering sales information *received from some other source* to
periodically update inventory. I can easily imagine that this company also
sells gift baskets comprised of various combinations of existing Products
and that they run a report or have some other means to restate those
GiftBasket sales into Product sales in order to create those
InventoryTransaction records. The wizard-created db just doesn't show this
step, but this is how you would need to create that translation/restatement.

 

[Bob Anderson]

After some time, I have come up with the following. I write this as I begin
to see if it will work. Does it seem a reasonable solution (first items in
listed below are primary key)?

For Sale Items (ItemID, ForSaleItemID)
Sale Item to Product Link (ForSaleItemID, ProductID01, ProductID02 ...
ProductID12)
Products (ProductID, ProductName, CategoryID, SalePrice, etc.)

I couldn't see how to do the number-of-items factor (in a table - a query,
yes) so I plan to simply list the same item as many times as necessary. The
relationships view looks might odd with so many links from ProductID## to
ProductID in the Products table, but Access didn't balk.

Bob Anderson

 

[Bob A.]

I believe that I have found a solution - posted here for posterity, no doubt
;-| What threw me most was not seeing the many-to-many relationship, and
thus the need for a composite key (first time) in the linking table.
Relationships are one-to-many on both sides of Assemblies table with the
many part on the Assembly side.

ItemsForSale(ForSaleID, ItemName) key is first item

Assemblies(ForSaleID, ProductID,UseFactor) key is first two items

Products(ProductID, ProductCategory, Price, etc.) key is first item



Bob Anderson