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

 

[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.

 

[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.

Google Search

 

[GaP42]

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

Only make changes and test the changes in a non-production database.
I hope you considered the advice in post #17 ? The aim is to have just one consolidated database.
Step 1: Figure out what you need to do to make the Product tables consistent in both Warehouse databases and make a universal product table for implementation in the one database. Make a detailed plan. What are the steps, timings, needs, conditions for doing it.
- Extract data from each database into a test database containing all records from each Product table. It must have the ProductID, WarehouseCode, ProductCODE (if available), ProductDesc, .... and a column for Matchedto (the to hold the Productt ID for the match)
- 2 queries one for Warehouse A one for B, showing the list of products, in ProductCODE order, or Product Description Order. Compare/eyeball the two lists for matching. If it matches up well, you can apply an update query to mark the Product record from Warehouse B that matches to the Product for Warehouse A (place the Product ID from Warehouse A in MatchedTo for Products from Warehouse B).
You might find near matches which should match but the Description does not quite align. Perhaps you might get the Product description changed in the PROD database so that when this process is repeated it does match.
You will likely have leftovers. That may require further (manual) effort. You might consider a Checked column to keep track of which records have been reviewed - update via the query and for those eyeballed and OK with or with no corresponding record. However I would expect that all your records should have a match however there may be items that are accepted and sold from Warehouse B or only within Warehouse A.
- This is a test. When you are satisfied with your procedures - and you should document them - you will need to run them against a fresh copy of the Production system AGAIN, check again. When you are confident you have the data in a good state - remembering that it must be suitable for your existing purchase and sales records - then you are ready to apply it finally to your new consolidated database - and have users swap to that - provided you have also prepared the front end for that change. (You cannot afford to support three databases - any further additions/deletions, mods to the Product Table in either warehouse needs to be reflected in the universal table. However you need to develop and design the consolidated database application which will incorporate the universal product table ensuring that your inventory controls work against the universal product table.
- That also means you need to have some users Test your consolidation and updated database inventory/sales/purchases before going to production.

Re SKU / ProdCODE - if you do not have this already you also need to work on this too as you complete/ develop and test your universal product table. Do your suppliers have an SKU or ProductCode marked on their products? (see again post 17 and the StockUnits table: it can handle what you receive in cases and can translate these to units for stocktake purposes.

There are many wrinkles in the process of migrating the data: what will you do about the historical Purchase and Sales records pointing to the old ProdID when using the new universal Product Table? The values are in the proposed table but you may need to consider an update to those. Reports for Sales and Purchases also need adjustment (including ones which span the changeover date).
Do not forget back up of both databases immediately prior to changeover.

 

[hfsitumo2001]

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.

In this 2 databases we have the same product_ID and descriptions. In Warehouse A, for purchase in other than EACH (Cases) and in B is purchase in EACH. if Housekeepers request in Cases, we take it from A, and we posting it in DATABASE A, if in EACH we take it from B. another activity is to Transfer from A to B, if the supplies was low in B, than we enter in the database A quantity in Case, and when we enter as receipt in B in equivalent EACH. in the form will tell us how many EACH in the CASE, so we can enter how many EACH as supplies IN, in Warehouse B. Database.

That's the way we handled it, and there is no problems, but I want to follow your guys way to have only one database.

Thank you.

Frank