Any solution for Multi Unit of Measures?

[hfsitumo2001]

Hello, right now for our Inventory system, I have not found solution to the disability of MS Access to handle multi unit of measures. The way I do now is to create 2 Databases. For the big ware house I call it Warehouse A, where we receive supplies in cases, 1 database. And in the small warehouse, we call it Warehouse B, where we give the supplies to the user in " Single Unit", I call it "EA". So if the user take the supplies in CASES, I use Database Warehouse A, if the user take the supplies in single unit, I use Database Warehouse B. If the supplies in the small warehouse was low, I transfer from Warehouse A to Warehouse B, and I fill quantity field in the Warehouse A database as Out in CASE, and In Warehouse B, I fill in the input form the quantity in EA. say if 1 case consists of 6 rolls of paper towel, in Warehouse A database I put 1 CA as out, and I put 6 EA in Warehouse B database.

Any one can give me the sample of Multi Unit of Measure in only One database?

Thank you,

Frank

 

[plog]

You make one inventory table and make the unit type (cases or units) a field to input. So that a record in your inventory table would look like this:

InventoryID, Warehouse, ProductID, Quantity, UnitType
1, A, 131, 4, Case
2, A, 131, -2, Case
3, B, 131, 10, Unit
4, B, 131, -7, Unit

Then if needed you can have a conversion table which tells you how many units per case of a unit.

 

[GaP42]

You receive "Cases" and distribute as "Cases" or "Units" (Each) from either Warehouse A or B. Warehouse A only handles Cases.

You also describe in Warehouse B - the eg Paper Towels - that even though they are in packets of 6 you sell them as Units, not as packets.

Inventory control should be dealing with the smallest units of the product. To combine the warehouse opertions you will need to fundamentally operate in the same terms of unit of product.

Database A seems to need to have a translation table for ProductCase to QuantityInCase, with an ability to support changes in the number of units a ProductCase may contain, by ProductID. You would then be able to relate/determine the units of a product in Warehouse A and Warehouse B together. Your inventory table must be able to, for Product Received, that the Product if provided as a UnitType of type CASE, that the translation table is utilised to determine the UnitsReceivedIssued in the inventory, and this is used to determine the UnitsInStock.
Once working in the same units you then need to consider how to merge db data.

Of course this is a general description for a solution you will need to apply

 

[Pat Hartman]

I have not found solution to the disability of MS Access to handle multi unit of measures.

I'm not sure I would blame this failing on Access. You haven't looked very hard. Every application I've ever written that deals with inventory handles multiple units of measures. You need a cross reference table to convert the multiple packs to eaches. One of them for a wholesale florist purchased bunches but sold eaches. A bunch was always 12. If you want to keep the cases, 6-packs, etc as items to sell then each type of package needs a different SKU. Frequently the price will also be different. A case might sell for $100 but if you break open the case, then each single item sells for $5. The only reason to ever convert is because you need to take a case out of inventory and add it back as 24 eaches.

Creating two databases has to be much more difficult to manage.

 

[gemma-the-husky]

Your situation needs a bit of thinking about. If your unittype is "pack" you may also need a quantity in the pack, maybe a different unit for each quantity, so a pack of 6 is distinguished from a pack of 12. You also need a setting to determine whether pricing is per item or per pack. Another setting for whether you permit packs to be split. So a pack of 6 eggs or 16 bags of crisps won't be sold as individual eggs or crisps, and a pack of 12 eggs is distinguished from a pack.of 6.

You may have the same with building materials. You might have a tonne pallet of 40 poly bags of sand, but you probably would sell the bags in smaller quantities.

So you need to think carefully about how you manage your products etc.

 

[Gasman]

I have not found solution to the disability of MS Access to handle multi unit of measures

A bad workman always blames the tools.

 

[ebs17]

If you are talking about multiple databases, you probably already have tables. To provide information about your development status, you should show the database schemas.

the disability of MS Access to handle multi unit of measures

MS Access is a development environment. Whether you can find a solution for such simple things as mentioned depends primarily on the developer's skills.

 

[GPGeorge]

Hmm. I think you could get some ideas about handling inventory from the Northwind Developer Edition template.

 

[gemma-the-husky]

Hello, right now for our Inventory system, I have not found solution to the disability of MS Access to handle multi unit of measures.

It's not a disability of ms access. It's to do with the designer designing the appropriate table structure to provide the facilities you need.

Lots of posters here have suggested things you might do to accomplish your requirements.

 

[MarkK]

A product like a can of beer should be a single row in a product table. A six-pack of those same cans should be a different row in the same product table. A four-pack of six-packs--a case--should be a different row too. And maybe in December you sell a "7 Beer Special", which is a six-pack and single, for one low price. It is also a distinct row in tProduct.

Then you need a join table to describe products--like the six-pack, the case, and the special--that are comprised of other products. In the join table there should be a foreign key to the parent tProduct row, and a foreign key to the child tProduct row, and a quantity that describes how many of the child products occur in the parent.

tProduct
ProductID
Name
Price

tProductComposite
ProductCompositeID
ParentID (FK to tProduct)
ChildID (FK to tProduct)
Quantity

With this structure it should be pretty simple to describe products that contain other products.

tProduct Data
ProductID, Name, Price
1, Beer, $2
2, Six-Pack Beer, $11
3, Case Beer, $40
4, 7 Beer Special ( a six-pack and a single ), $12

tProductComposite Data
ProductCompositeID, ParentID, ChildID, Quantity
1, 3, 2, 4
2, 2, 1, 6
3, 4, 1, 1 ( note how the 7 Beer Special is a composite of 2 different products... )
4, 4, 2, 1 ( ...so our composite table has 2 rows with ParentID = 4! )

This is how I would create a structure for products that contain quantities of other products. : )
hth

 

[hfsitumo2001]

You make one inventory table and make the unit type (cases or units) a field to input. So that a record in your inventory table would look like this:

InventoryID, Warehouse, ProductID, Quantity, UnitType
1, A, 131, 4, Case
2, A, 131, -2, Case
3, B, 131, 10, Unit
4, B, 131, -7, Unit

Then if needed you can have a conversion table which tells you how many units per case of a unit.

Then if needed you can have a conversion table which tells you how many units per case of a unit.

How can I relate the inventory ID with the conversion table

Frank

 

[plog]

Via a table that contains that information. It should include the ProductID and number of units per case.

 

[DickyP]

The core to this 'problem' isn't Access but proper relational database design. The data relationships exist even if you were doing the process with pen and paper.

 

[hfsitumo2001]

Via a table that contains that information. It should include the ProductID and number of units per case.

I think the relationships to be used here are all One to Many, Is n't?

 

[hfsitumo2001]

Inventory control should be dealing with the smallest units of the product. To combine the warehouse opertions you will need to fundamentally operate in the same terms of unit of product.

Yes, I think I need to learn this approach "the smallest Unit of the product". Could you explain more on this approach. For your information in fact we have 2 warehouses beside the office, at the back and in front of the office, therefore I have 2 ms Access database for Warehouse A and for WArehouse B. And the way I deal with the quantity of the database is like I explained in my initial posting. Now I need to change it to only one database. how can I change my tables relationship. I think I need to make a big change, because in dealing of price also is included in the Stock tabble, so onnce I change the price, the price in all reports pervious period or current periol will change. Because we more control on Quantity on hand, usage/outgoing and incoming.

Thank you for any helps.

Frank

 

[Gasman]

I have 2 ms Access database for Warehouse A and for WArehouse B.

You only should have had one table with a field that holds which warehouse.

because in dealing of price also is included in the Stock tabble, so onnce I change the price, the price in all reports pervious period or current periol will change.

In that case, you would store the price at sale time.

 

[GaP42]

Two databases - identical in structure, are used for Warehouse A and Warehouse B. (Your current situation)
The Product table: While you have a PK called ProdID, as you have two databases, there is no correspondence between the product id in Warehouse db A and product id in warehouse db B.
Do you have a unique CODE for each Product consistent across both databases? (An SKU) or do you rely upon the Name/Description of the Product to be able to say ProdID 123 in db A is the same as ProdID 987 in db B?

What you will need to do is firstly create a consolidated Product table for the two warehouses.
This may/should be of the form:

UniqueProdID (PK for this table)
WarehouseCode (A/B)
ProdID (FK to Product table from each db)
ProdCODE (unique for a product across the two warehouses)
StockUnitFK (see below)
UnitCost
UnitPrice
.... etc

Investigate and resolve issues with inconsistency in product lists across the two db.

Develop a table for StockUnits like

StockUnitID
ProdCODE
UnitofStock (Case, Pkt, Each, etc)
QuantityPerUnit (100, 50, 12, 6, 1 etc)
DateActiveFrom
IsActive
....

Assuming that you receive lets say 6 Cases of Widget 1. Each Case contains 100 of the Widgets. The Widgets have a ProductCode XYZ used across the two warehouses/ and databases. You sell these Widgets as single units, or Cases

UniqueProdID:	56615625	9566156
Warehouse:	A	B
ProdID:	234565	457698
ProdCODE:	WIDGETXYZ	WIDGETXYZ
StockUnitFK:	01	02
UnitCost:	(the cost paid for the unit designated in StockUnit record)	(The cost paid of a Unit of WIDGET XYZ)
UnitPrice:	(the price to be paid for the StockUnit)	(The price to be paid per unit)
....

Stock Unit:	01	02
ProdCODE:	WIDGETXYZ	WIDGETXYZ
UnitofStock:	Case	Unit
QuantityPerStockUnit:	100	1
DateActiveFrom:	Earliest date that this PRODUCT was acquired (as Cases of 100)	(same as for stock unit 01)
isActive: Y	the product is able to be acquired in these units

So you can then use your transactions / stocktake to calculate the quantities in hand (in the base units you use) for a product using the appropriate calculations through the StockUnit table for WIDGETXYZ (as well as report it in the StockUnits you have available, and in which warehouse)

Ultimately you will consolidate the 2 db to one, and no longer use the old ProdID.

Does this seem to meet your needs? Good luck.

 

[Pat Hartman]

Yes, I think I need to learn this approach "the smallest Unit of the product". Could you explain more on this approach. For your information in fact we have 2 warehouses beside the office, at the back and in front of the office, therefore I have 2 ms Access database for Warehouse A and for WArehouse B.

Let me jump onto this bandwagon. You need ONE database. ONE table for ALL inventory. Add a warehouse table so you can tell which in which warehouse the items are located.

And, the suggestion to keep the inventory in the smallest unit of measure makes processing significantly easier. But you still need individual SKU's for each package you sell.

 

[hfsitumo2001]

But you still need individual SKU's for each package you sell.

What does SKU stand for Pat.

Now I am thinking how can I modify my database while it is already go live. This is my input form for supplies out. To indicate warehouse, I should add the field in the sub form at the very left inventory sub form?.

But I still be having a problem on the quantity type. I think that should be a field for the basis unit of measure which is EA. Whether we sell or receive should start everything from EA then there is a conversion field to arrive at used Unit of Measure (Case) or in EA in case we receive or sell the supplies.

I appreciate any help or giving a sample database

Thank you

Frank

 

[Gasman]

I have Google on my phone.

sku - Google Search