Any solution for Multi Unit of Measures?

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

 

[Pat Hartman]

What does SKU stand for Pat.

Sorry, this is a pretty common inventory acronym. It stands for Stock Keeping Unit.

Restructuring tables once they are populated takes a little finesse. Since you can't make the schema changes and code changes in a weekend, you need to build a procedure that does at least the data conversion part for you.

Make copies of the existing BE's. You will work with them until you have a procedure that works and eventually you will run the procedure against the production databases when you are ready to go live with the new changes.

Build a new BE with all the tables that will remain as is and use simple append queries to populate those tables. If you have relationships defined, you need to be careful to keep the old PK values and to run the queries in a logical order so you don't break RI with the appends.

Make a new, empty, version of the inventory table. Add the new columns that you will need to define the SKU and UOM and any other new features. If you go with the SKU concept (and you should), you have a column that defines the product and that is a FK to the product table. Each SKU of diet pepsi, has a FK to the product that defines diet pepsi but the SKU's are for the UOM. Single, six pack, case, etc. Having a product table allows you to make summary reports so you can group all the sales of diet pepsi regardless of the SKU together.

Once you can convert your data to the new format, make the changes to the code and queries and forms. Test everything thoroughly. Then pick a weekend to run the conversion. Since you've never done this before, you want plenty of time and you don't want to interfere with normal production so you MUST do this off hours. Copy the empty BE template and run the procedure that runs the queries that populates the tables. Once that is done, rename the old BE and FE and move them to the backup folder. Move the new versions to the production locations and relink the BE. Test thoroughly.

 

[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

 

[GaP42]

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

Frank, you should work towards having one database, which was advised from the start of this thread. You need to work out how you will consolidate the product list from each of the 2 databases. An important feature will be to ensure a column is maintained that indicates the location of the stock - Warehouse A or B (for any one line item in the product table).

You say you have the same Product ID and descriptions in both Warehouses, then you have no problem doing consolidation do you? That will not be true if ProductID is an autonumber PK. It is more likely to be a Product Code (SKU) assigned manually to a product when the record is added to each db.

So test it - get hold of a copy of each Product table for the 2 databases, carry out a test merge. Inspect, review. Did you lose data? Is Product ID used as your PK? If so then what problems would you now encounter if that continued on this merged table? What does that imply about the changes you need to enact in the structure of the Product table in the proposed new database?

You will still need to carry out transactions that represent the movement of goods from one to the other warehouse. Consider the advice already provided, particularly in posts #21 and #22 to work out how you will do it.

 

[hfsitumo2001]

You say you have the same Product ID and descriptions in both Warehouses, then you have no problem doing consolidation do you? That will not be true if ProductID is an autonumber PK. It is more likely to be a Product Code (SKU) assigned manually to a product when the record is added to each db.

Yes it is not an auto-number, and also for the price, in the input form in Warehouse A, there is a calculated field to show price per EA, then when I add manually the new product, I put that price for the price per EA. So I can say my Unit Type are only 2, CASE and EA. But I still interested in making it into one database like all of you suggest.
I will be asking all of you more questions, especially on filling out the UOM type column in the form and how to create that columns

Thank you,

Frank

 

[hfsitumo2001]

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

With this structure, I still do not know how can I make it, if the same supplies item let's say paper towel was running out in Warehouse B (small warehouse) in the front of our housekeeping office, and I have to move from WArehouse A ( the Big warehouse in the leftside of the rightside of the office, we moved 1 case ( cosists of 6 rolls) from Whse A to B). For this transfer we have special log and what I did is opening DATABASE A and key in out 1 case, then open DATABSE B then keyin IN 6 rolls or 6 EA.

In each database there is a warning alert if the supplies is already in low level

In the case of making only one database, how can I make this kind of transfer of supplies. Also in my current system, I can see in the form in real time how much is the quantity on hand per Warerehouse. Can we make it too in one database?.

Thank you for any helps.

Frank

 

[plog]

Correct me if I am wrong, but you used a ton of words to ask this question:

When I transfer 6 units of paper towels from warehouse A to warehouse B, what does the data look like?

InventoryID is an auto number primary key, let's assume 131 is the ProductID for paper towels. You make 2 entries into the inventory table, a debit and a credit:

InventoryID, Warehouse, ProductID, Quantity, UnitType
1, A, 131, -6, Unit
2, B, 131, 6, Unit

 

[CJ_London]

I can see in the form in real time how much is the quantity on hand per Warerehouse. Can we make it too in one database?.

Of course - just filter or group by the warehouse ID

 

[hfsitumo2001]

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.

Hi Pat, although One database and One Tablie for all inventory. If there is a transfer of supplies between the warehouse, we still need to key in them right. And my qustion is whas is the name of field of the quantity to be input, because let’s say in warehouse A (the big warehouse) it is in Case, while in Warehouse B (the smaller Warehouse from which usually workers taking supplies) is in EA (smaller unit measure). I am still quite confused. Can you or anyone post here the sample of the Input form for the supplies requests?

Thank you.
Frank

 

[Pat Hartman]

Each inventory transaction has a from and a to component when the movement is internal. You create TWO transactions. One to subtract from the "from" location and one to add to the "to" location. This is the method that allows you to sum the transactions to get an on-hand number for an item. I generally add the ID of the "from" trans to the "to" record so you can match them easily for reporting. That's because I usually create the "from" transaction first. If you create the "to" transaction first, you would store the "to" ID in the "from" record.

Somewhere earlier in the thread we talked about you having to have a translation table. Items are stored as Units (ONE of something) or packages (multiples of something). The packages need to include the number of units. 12 or 6 or whatever. So you know that the from UOM is units and the to UOM is sixpack so to do the conversion you need to know if you are going from larger to smaller or smaller to larger so you know whether to multiply or divide.

Someone might have time to write a function for you but I'm not sure we have enough details. Try creating the conversion table and providing a list of transaction types and what you expect to happen so we at least have a shot at coming up with something useful.

Are you using the transaction method now? Are you keeping a separate transaction table for each warehouse?

 

[hfsitumo2001]

Are you using the transaction method now? Are you keeping a separate transaction table for each warehouse?

Yes Pat, I have a transaction method, and 2 databases for the 2 Warehouses. Each transacion for a users will create a new Id, this good for trancking it in the reports.

On other things price of the supplies should be in other table right. On thing I forseee how can We make the last price will be used for the price of the quantity balance on hand.

Thank you for yr helps

Frank

 

[Pat Hartman]

You're welcome but I'm sorry to hear you are sticking with the two database method.

 

[JimChristmas]

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

Within our inventory here, I have a table 'Part' which identifies what an item is. Then I have another table, 'SKU' that specifies a way that item may be sold. So, SKU might contain Part#1 as a 'PackIn' of 6. A Part class handles translation between the different levels.
Prices require another level to this structure. SKU might hold a 'CurrentCost,' but for historical reasons, a 'Price' table houses a complete history of SKU prices and when they were effective.

 

[Pat Hartman]

Is there a question you need answered?

 

[gemma-the-husky]

Hi Pat, although One database and One Tablie for all inventory. If there is a transfer of supplies between the warehouse, we still need to key in them right. And my qustion is whas is the name of field of the quantity to be input, because let’s say in warehouse A (the big warehouse) it is in Case, while in Warehouse B (the smaller Warehouse from which usually workers taking supplies) is in EA (smaller unit measure). I am still quite confused. Can you or anyone post here the sample of the Input form for the supplies requests?

Thank you.
Frank

Just to add a comment.

Well if you are transferring stock from location A to location B, then you need to add a minus transaction to indicate that location A, product X, pack type P1, has a new transaction of negative Q1,

So you might have moved -100 widgets from location A

Now you also create a plus transaction in location B, product X, packtype P2, with a positive quantity Q2, say 10.

If both products have the same pack type, both quantities will be the same, but equal and opposite sign. If they have different packtypes, then the quantities need to respect that.


So 100 of EAch may be the same as 10 packs of qty 10.

That's what your app needs to do to maintain the integrity of the system.

These 2 transactions might also need a date, and a transaction ref no. of some sort, details of the person making the transfer, etc.

The price/value of these adjustments is a different matter and depends on your costing system. It's not a given that 100 widgets has the same value as 10 packs of 10 widgets.

You may not need to even consider stock values when manipulating quantities, as that's really a different function.

 

[Pat Hartman]

Let me answer this another way. You need an easy way to figure out the SKU for the "to" SKU. Since you can go from 1 - 6 or from 6-12 or from 12-1, etc. they give you the "from" SKU. Then they either need to give you the "to" SKU or simpler, the UOM. Once you know what the "to" UOM is, you can use the productID and the UOM to look up the target SKU. This is probably safer than allowing the user to pick it since he could pick a from SKU for toilet paper and a to SKU for paper towels. Once you know the from/to UOM's, it is simple math. But, you still need to validate that the conversion will work. If the user picks 13 EA that can't evenly convert to either six-pack or doz.

Receive and Sell transactions create a single record but a transfer transaction must create two records. It must subtract from one SKU and add to a different SKU.

 

[gemma-the-husky]

But all of this can't be magically done by AI.

The developer needs to understand the problem, and write code to manage the transactions accordingly. You can probably develop a function to take any quantity for a given SKU, and translate that into an appropriate quantity for another SKU, (the same SKU is trivial, clearly, but a different SKU may or may not be doable, as Pat just pointed out, so your function must return a success/failure flag as well as the relevant quantity. Maybe a return value of zero would serve as a failure indicator). This isn't particularly difficult, but it's not an intrinsic arithmetic calculation either.

 

[hfsitumo2001]

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.

what about the field for the quantity balance of Inventory, is it calculated field, or a saved field.

Thank you,

Frank

 

[GPGeorge]

what about the field for the quantity balance of Inventory, is it calculated field, or a saved field.

Thank you,

Frank

In a properly designed table, inventory balance is not a calculated field.

Check out the Northwind Developers Edition Template for a working example of managing inventory.

Check out the explanation of inventory in NW by two of the developers who created the template.