Tracking Inventory with Multipacks

[CaneRivero]

I'm trying to design a simple (I hope) Access 2007 data base for tracking inventory. Presently I use Excel 2007.

I sell multi-packs of certain items and would like Access to deduct the correct number of items when I make a sale.



Example: I sell gum by the 1-stick, 10-stick pack and the 100-stick carton.

If I have 1000 sticks in inventory and sell 1-stick and a 10-stick pack I need Access to deduct 11 sticks from 1000.

In Excel I have used a SKU like this: 001.StickGum, 010.StickGum, 100.StickGum. Then I use Text-To-Columns to create 2 columns - the 1st with the number of sticks and the second with StickGum.

TIA,
Cane

[June7]

Review: http://allenbrowne.com/AppInventory.html

Basically: calculate how many sticks purchased, calculate how many sticks sold, calculate the difference for balance on hand.

[NTC]

I believe your issue is managing the translation of words to numbers. In your table you need to have a field representing the true quantity. So that when the words are being viewed/selected by the user i.e.
1-stick
10-stick pack
100-stick carton
somewhere else in parallel are the fields with actual quantities doing the real math......

So right now you may have your table set up to have Qty 1 of 100-stick carton - - you need to expand your table and have another column/field 'ActualQTY' that enters in & uses the value 100

[June7]

Another column might not be required. The quantity can be extracted from those SKUs.

Val("001.StickGum")

returns 1

Val("010.StickGum")

returns 10

Val("100.StickGum")

returns 100

[CaneRivero]

I believe your issue is managing the translation of words to numbers. In your table you need to have a field representing the true quantity. So that when the words are being viewed/selected by the user i.e.
1-stick
10-stick pack
100-stick carton
somewhere else in parallel are the fields with actual quantities doing the real math......

So right now you may have your table set up to have Qty 1 of 100-stick carton - - you need to expand your table and have another column/field 'ActualQTY' that enters in & uses the value 100

Thank you for your reply - I appreciate your time.

I should have mentioned I am trying to keep the format because that is the way it comes to me in a download.

I don't have a table set up yet...I'm still trying to design it...

Regards,
Cane

[CaneRivero]

Another column might not be required. The quantity can be extracted from those SKUs.

Val("001.StickGum")

returns 1

Val("010.StickGum")

returns 10

Val("100.StickGum")

returns 100

Thank you for your reply and your time.

This sounds like it would work well. I looked up VAL so see how to use it and it stops reading at the period. May I ask how you would suggest I get the DB to attribute those numbers to the correct product?

BTW thanks too for the link to allenbrowne.com - I was planning on make the very mistake he warns against.

Best regards,
Cane

[June7]

What 'download' are you referring to? Is this done periodically?

I don't understand the difficulty of attributing numbers to the correct product. Your unit/product is a combined value. If you select the ID for record 100.StickGum when creating an order or sale record, the association is accomplished by this primary key/foreign key relationship.

[CaneRivero]

Thank you for your patience.

I get a daily download of my previous day's sales that includes a column of SKUs (and a column of how many of each SKU).

I believe I misstated my premise in my 1st message. I apologize.

I have a box of 1000 sticks of gum. I sell them in sets of 1, 10 and 100.

The data I receive looks like this:

1 010.stickgum
3 100.stickgum
10 001.mints
1 001.stickgum
5 001.stickgum
10 005.mints

Presently I use Excel to manually break the SKU into two columns so it looks like this:

1 10 stickgum
3 100 stickgum
10 1 mints
1 1 stickgum
5 1 stickgum
10 5 mints

I have a formula that, after doing a sort, tells me I sold:

316 stickgum
55 mints

and it subtracts those numbers from inventory onhand.

I can change the SKUs to anything I want if it will work better. Those are what I came up with specifically to get Excel to do what I needed.

Thanks again,
Cane

[June7]

Still don't understand the issue.

You buy 1000 sticks of gum, you sell 316, balance on hand is 684.