Current Inventory Calculations (2 Viewers)

[RobertWaring]

Hello All. This is my first post and for that matter my first database build. Arguably not one of my best decisions, but I am determined to figure this out. I am okay with my VBA skills, but they are limited to Microsoft Excel. I am trying to create an inventory database as it is the only option available to me that can be used my multiple users simultaneously with all having the same current data - you know, what a database was designed for. (lol)
Ive gotten pretty far but am stuck trying to build a rather complex query (to me anyhow) to get an accurate count of inventory items per location. The query is based on the below table:

It lists each transaction by type (Receipt, Shipment, Adjustment, Move, CycleCount). These should determine how the quantities are totaled. There are 4 additional key fields. The FromLocationType, FromLocationID, ToLocationType, and ToLocationID. These serve to determine where product came from and where it went. Each type has three posibilities (Customer, Supplier, Warehouse). In case your wondering why, we rent - but do provide consumables that are tracked for billing purposes. The To/FromIDs are to differentiate which specific location based on the type. If this were excel I could figure this relatively easily, but being as I am VERY new to Access - I am lost!! I have the following query (qryTransactionSummary), but it doesn't calculate the totals correctly. I can get close, but it goes without saying that that's not good enough.

SELECT InventoryTransactions.ItemID, InventoryTransactions.ToLocationType, InventoryTransactions.ToLocationID, Sum(Switch([TransactionType]="Receipt",[Quantity],[TransactionType]="Adjustment",[Quantity],[TransactionType]="Move",[Quantity],[TransactionType]="Shipment",-[Quantity],[TransactionType]="CycleCount",[Quantity],True,0)) AS NetQuantity, InventoryItems.[Item#], InventoryItems.VendorItemName, InventoryItems.CasePrice, InventoryItems.PackCount, InventoryItems.PackSize, InventoryItems.PackUOM, InventoryTransactions.FromLocationType, InventoryTransactions.FromLocationID
FROM InventoryItems INNER JOIN InventoryTransactions ON InventoryItems.InventoryID = InventoryTransactions.ItemID
GROUP BY InventoryTransactions.ItemID, InventoryTransactions.ToLocationType, InventoryTransactions.ToLocationID, InventoryItems.[Item#], InventoryItems.VendorItemName, InventoryItems.CasePrice, InventoryItems.PackCount, InventoryItems.PackSize, InventoryItems.PackUOM, InventoryTransactions.FromLocationType, InventoryTransactions.FromLocationID;

To sum it up - Any transactiontype=Receipt, shipment, or Move need to be added to the ToLocationID(based on type) but also subtracted from the FromLocationID(based on type)
Any transactiontype=Adjustment or CycleCount needs to be subtracted from the ToLocationID(based on type)
The quantity is in the Quantity field in the table. It will ALWAYS be a positive value with the exception of the Adjustments or CycleCounts as this quantity is the value of the variance (which is stored on separate tables, merely trandferred to this table for convenience.)

Can someone please help me determine what I am doing wrong - bare in mind I am a complete novice, so easy on the terminology (lol). Thank you in advance! - Robert

PS If more information is required or this doesn't make sense, please let me know.

 

[plog]

You need to tweak your tables and also the way you handle moving inventory. Moving inventory should involve 2 records in your InventoryTransactions table--a debit from one location and a credit to another, not just one record that handles both things. With ToLocationID, TransactionDate and Quantity we should be able to logically determine what credit goes to what debit if needed.

Then for your tables:

1. You don't need 3 location fields in InventoryTransactions. You've essentially got 3 levels going from large to small---LocationType, LocationID, BinID. You only need to store the smallest level you want to know about and then relate the other information in another table. So, BinID is the only location field you need in InventoryTransactions. Then you have another table that lists all the Bins, the locations they belong too and the location type. When you need all that data together you make a query and JOIN that data to InventoryTransactions.

2. You need a TransactionType table. That long Switch statement that determines if a type is a credit or a debit needs to go away and into a table that has a field that holds 1 or -1 to use to multiply the Quantity by. That table will also allow you to add more transactions now(e.g. MoveFrom, MoveTo, InitialStock). And in the future if you need to add more you won't have to recode that Switch statement but just add those new transaction types to the table and the query will do the appropriate math based on the data in the table.

3. Is ItemCost needed here? I don't know, but seems like it only applies to acquisitions, not to sales or even moves. Do you really need to know the cost of every thing put into inventory? Or just the current marke price/cost?

With all that said, once you make those changes, getting inventory is super simple:

SELECT InventoryTransactions.BinID, InventoryTransactions.ItemID, SUM(InventoryTransactions.Quantity]*TransType.[TransValue]) AS NetQuantity
FROM InventoryTransactions INNER JOIN TransType ON InventoryTransactions.TransTypeID = TransType.TransTypeID
GROUP BY InventoryTransactions.BinID, InventoryTransactions.ItemID

 

[The_Doc_Man]

I'm going to make a constructive but sideways suggestion. You would do yourself a TREMENDOUS favor by searching for "Database Normalization" as a way to control a potential runaway nightmare of a database. My standard advice - in this forum look for "Normalization" because we ARE a database forum, mostly. From the general web, your search would have to be "Database Normalization" because other disciplines use the word "Normalization" as well - math, chemistry, diplomacy, pharmacology, ... so you need to specify the type of normalization. When you get a list of "hits" in your search, try reading articles from the .EDU domain first, .COM articles later. The .EDU domain is going to be from colleges and universities and will likely be very accurate (and a bit dry.) The .COM hits will be OK, but they are more likely going to try to sell you something. Looking at two or three articles from each domain should probably get you straightened out on that subject.

Among other things, having a relational database means that you can take advantage of relations such as plog described in his point #1 of post #2. If you have a unique ID for the lowest item, you can derive all of the other parts of the location in a query or set of queries. It takes a while for you to see it, but normalization is a way to harness the power of Access and tame your raging database.

 

[LarryE]

Could you describe your business model for us? The design of all ACCESS projects follow the business model of how you do business.

1. What business are you in? Do have a warehousing business?
2. Whose inventory is housed there?
3. What kind of transactions ADD to the inventory (Purchases?)
4. What kinds of transactions DEDUCT from inventory (Sales?)
5. Does inventory move from one location to another? From what you have told us, it might.
6. What do want to track? Purchases? Sales? Inventory relocations? All of these?

From what I can gather:

1. You have a warehouse (or maybe more)
2. This warehouse contains several numbered locations
3. Inventory can be added, deducted and relocated from one location to another

It sounds like you will need at least:

1. An Inventory table holding inventory items (Item numbers, Purchase Cost, Description etc.)
2. An Inventory Location table holding all the location numbers and other location identifiers
3. A Transaction table to hold Transaction Date, Adds, Deductions and Relocations within the warehouse (Inventory values and quantities held is calculated on forms and reports. They should not be held in a table field)

The design might be something like this:

1. Each Inventory Item may be held in multiple locations throughout time
2. Each Location has multiple Bins
3. Each Location Bin may have multiple transaction types throughout time (inventory items added, deducted or relocated to or from). Since there appears to be only 3 transaction types, I would use an Option control on a form where an add to inventory=TransactionTypeNumber 1, a deduction from inventory=TransactionTypeNumber 2 and a relocation from one Bin to another=TransactionTypeNumber 3.

Does this sound like what you have and what you want to track?

If you wish, you can attach your file. I can then do my best to normalize your data and transfer it into the appropriate tables.

 

[Pat Hartman]

No one has mentioned Northwinds but the two new sample databases are far more rational examples than the old version. The "big" version does an accurate inventory so you might want to open Access and instantiate that sample and study it closely. There is documentation which I'm sure you can find links to AND one of our experts here was part of the team that created the samples so you can almost always get a pretty detailed answer to questions by tagging @GPGeorge . Even if you decide to stick with your own database, the ideas you will see implemented in Northwinds could save you a lot of design time even if you choose to implement them yourself.

 

[GPGeorge]

Credit where credit is due. Kim Young was responsible for most of the inventory logic in NW Developers Version, though.

 

[tvanstiphout]

Credit where credit is due. Kim Young was responsible for most of the inventory logic in NW Developers Version, though.

Here is the video with the Inventory presentation: