Inventory Transactions

[mm26]

Hi,
I am attempting to create an Inventory Database for our Installation Services Company. I am trying to figure out the best way to structure my database to enable the calculation of: Quantity on Hand, Quantity Allocated, Quantity on Order and Product Cost. I am not sure if I will be able to use one Transactions Table for the different scenarios (Purchase Orders-Incoming with cost data), (Adjustments-inventory count adjustments--I don't believe this should include cost info?) (Sales Orders-initially neutral transactions until received in warehouse) Rather than storing product cost in the product table, I would like it to be calculated based on past purchase orders (we purchase most material per job, so prices vary)

I have created the following tables and just wanted to make sure I am on the right track:

TransDirectionT
1-Incoming
2-Outgoing
3-Neutral

TransTypeT

Purchased (3)
Received (1)
Allocated (3)
Delivered (2)
Transfer (2)
AdjustUp (1)
AdjustDown (2)


TransactionT
TransactionID (PK)
TransTypeID (FK)
Order
employeeID
TransDate
ProductID (FK)
Quantity
UnitPrice
Comments



Thank you so much!!

[June7]

Have you looked at the MS Desktop Product Inventory template database available for download?

Will you be tracking invoices and purchase orders?

Also review http://allenbrowne.com/AppInventory.html

[mm26]

Thank you! Yes I have downloaded and reviewed the Northwind Database and have seen Allen Browne's post. (I have also viewed many of Richard Rost's tutorials and read posts from "Scottgem at Wordpress") I don't need to track invoices, but do need to track Sales Quotes, Purchase Orders and Material received and Delivered. I thought the transaction scenario that Scottgem described might work best for me, since I would like to track material movement.

[orange]

Do you have some test data, or test scenarios? Have you run the scenarios against your data model?
If not, then that is where I'd go next. Test it and record and reconcile every anomaly. The critical part is to get your tables and relationships to support your business rules. Richard's videos are very good.

Have you clearly defined A SalesQuote and PurchaseOrder?


ALSO: Is this you?

[mm26]

HI Orange, Funny...no that isn't me but looks like my post...will have to look at the answers there too

I have added all of the part numbers in our warehouse (all at qty 0) and created an Adjustment form to start. Before I continued with the Sales Order and Purchase Order I wanted advice as to whether I can use the same transaction table for all three "forms": Adjustment Form, Sales Order, Purchase Order or if I should have separate transaction tables for each (When you make adjustments you really don't know the cost --unless there is a way to do some kind of FIFO selection?) A sales order should probably convert to a PO and have the costs there? Once I am a little more clear on the mechanics, I wanted to finish my Sales Order form, create a PO form and test.


FYI...Information from Scottgem:
Hi,
I found your instructions on inventory transaction tablesand had a question on how this table is actually populated. It seems you need to use the Purchase Orderand Sales Order details for the Incoming and Outgoing transactions, but I donot quite understand the mechanics of it all. If you wouldn't mind elaborating on this portion of the process, I wouldgreatly appreciate it! Thank you!!

Answer:
The way I generally do it is with ONE transactions table.This table replaces both the Purchase and Sales details tables so you have onetable that records ALL movement of stock.

You could use separate details tables if you want and usea Union query to pull them together do do the AdjQty query that I speak about.

http://en.allexperts.com/q/Using-MS-Access-1440/2012/4/database-stock-control.htm

http://en.allexperts.com/q/Using-MS-Access-1440/2010/8/Recieved-transactions.htm

[orange]

Make sure your tables support your business before getting into the nitty gritty of Access.

You may want to check out this sub/super type tutorial.
Here's another tutorial on database design with a process to create tables and relationships based on business description.
And here's an older post of mine that deals with FIFO data base the Poster had found.

[mm26]

Thanks Orange, the average costing system looks more manageable! This project is more than I bargained for!!

[mm26]

Would I be able to store Sale IDs, PO IDs and Adjustment IDs in the Order field of the transaction table (these IDs come from 3 different tables)?

So I was thinking of adding:
OrderType to the transaction table: PO, SO or Adjustment. I would only calculate average cost using POs and only calculate average price using SOs. That way I am thinking I could use 1 transaction table for all material movement. Just wondering if anyone sees any potential problems with this. Thank you!!