Any solution for Multi Unit of Measures?

[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

 

[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

 

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

 

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

 

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