BASIC Inventory Transactions to Quantity On Hand Issues

[landono0660]

Hello all,

I'm new to Access and am starting up a very simple lab inventory database. The transactions are simple and there are no aspects of sales involved. A lab worker needs to stock material on the floor, he removes what he needs and then logs a transaction in the database.

I have a MasterInventory table and an ItemTransactions table I'm working with that I can't quite figure out. I understand that you shouldn't store aggregate data and a way to do this is through Queries. I have created one Query using an IIF() expression that reads Addition/Removal from TransactionTypes, and then either adds or subtracts this from MasterInventory.QuantityOnHand to give a "Current Inventory". This is fine but every transaction that has been made for that item with add or subtract from that initial QuantityOnHand value. I'm thinking of actually changing QuantityOnHand to BaseQuantity or StartupQTY. I believe now from what I've read on this forum is it may be better to set up a query based off ItemTransaction.Quantity totals.

With all that being said I'm open to suggestions. This database has no data yet, I'm preparing it for an initial dump after we finalize stock. If you would like I could send what I have built so far.

Thanks,

Landon O.

 

[plog]

Since you have no data in there, can you post your database? I think you need to zip it then upload.

Or, complete the Relationship Tool with both tables, related them appropriately, then expand both tables so we can see all fields then take a screenshot and post that.

With that we can help you get to where you need to go.

 

[pbaldy]

I wouldn't have a field in the inventory table, I'd simply create an addition transaction for the initial quantity. Then your quantity on hand is only querying the transaction table.

 

[The_Doc_Man]

In a full-blown, sales-oriented inventory, you simply have as many transaction types as you need and build a table of whether the transaction is an ADD or DRAW. You always start with 0 inventory using this method, so you might have a transaction type of "Stock Delivered" (an ADD) to start the operation. You can have transactions for "Stock Issued" (a DRAW), "Stock Returned" (an ADD), "Stock Breakage" (a DRAW), ... There is also the special case where you do a special COUNT (or SIGHT) inventory, in which you flag all entries before that inventory so that you don't count them in the summation query. Some variation on this idea might help you.

 

[gemma-the-husky]

@landono0660
Just to confirm - you don't mean you are trying to maintain a field in your items table for "current inventory", do you?
You are just calculating the inventory in your query when you need it?

 

[landono0660]

I think from what I've looked into so far I'm going to keep an InitialQuantity or BaseQuantity field in my MasterInventory to serve as comparative point when I run the query (unless it's absolutely not needed). Ideally in the end I would also like some form of automation or pop up to flag when I've gone below a minimum stock. There aren't any 'sales' involved with this really it's just to track what's being taken from a tool crib and to identify when we need to order more. I've attached a sample of what I've got so far. If you have a chance to look through it please let me know if you see any other unnecessary fields or objects. Thanks!

 

[plog]

TransactionTypeTable isn't needed in its present form. If you just want to track in and out, you use positive and negative numbers in [Quantity] and do away with that table completely. If you want to distinguish between actual transaction types (Initial Stock, Spoilage, Return, Acquire Inventory, etc) then you can use a TransactionTypeTable and you have 3 fields:

TransactionTypeTable
tt_ID, autonumber, primary key
tt_Type, text, description of transaction (Initial Stock, Spoilage, etc)
tt_Value, number, 1 for credit to inventory, -1 for debit to inventory

In either case you get rid of BaseQuantity. It just goes into ItemTransactions.

I'm not sure MasterInventory is correct, at least not named correctly. It might need to be a few tables. Suppose you have 3 suppliers that supply item A--that makes 3 records in MasterInventory, one per supplier. If A is to be located in 2 different places now you will need 6 different records--1 per supplier/location. I don't think that's correct. Do you want to track items in a location all the way back to their supplier?

Depending on how grainularly you want to track items, you might need a new table, or you might just need to move Location and Supplier fields to ItemTransactions.

 

[landono0660]

We won't have that many transaction types yet. Just Add/Remove. So I should probably do away with transaction type. I feel like it was unnecessarily causing complications from the beginning. The only way I can see it helping is if we use a barcode scanner to enter quantities I don't know if that controller could enter a negative value in the field.

For the Supplier table/field I need to think about that some. It's likely if it's concerning a generic part like a bolt or screw that any major one supplier would do (ie. Amazon, Lowe's, Home Depot). For specific lab equipment those would only have one place they come from in most cases so I'm not sure if I need to do away with that system.

Otherwise, how would you suggest I go about setting up tables for instances where 1 Item has multiple vendors?

Thank you.

 

[plog]

It depends. First seperate Items and Inventory in your mind. You have on table for unique items (nut, bolt, nail) and you have another table where you track the inventory of those items (5 bolts in, 7 nuts out). Do you care where specific nuts and bolts come from when you put them into inventory? Or do you just need to keep a list of suppliers where you can get those items?

Most likely once you have a bunch of nails you don't care where they came from, they are just now in inventory. In that case you create a new table that will lie between Suppliers and Items, lets call it ItemSuppliers it would look like this:

ItemSuppliers
is_ID, autonumber, primary key
ID_Item, number, foreign key back to ItemID in Items table
ID_Supplier, number, foreign key back to ID in Suppliers table

That table now determines where you can get specific items. If Lowes and Home Depot have a specific screw, you put 2 entries into ItemSuppliers to capture that (one for each supplier).