access 2010 relationships

Hi Experts,

I'm a newbie and am trying to build an inventory database.  I want the database to retain each period's physical count and the pricing at the time of the physical count.  Stated differently, I want to be able to track historical physical inventory periods (both quantity of goods and their pricing).  I keep running into problems creating reports for staff to perform the physical count of the items at various facilities and locations within the facilities and then closely replicating the order of these physical count reports in a form so the user may enter these quantities accurately.  I'm now at the point of wondering if it's my inexperience creating forms and reports or whether the overall structure of the database is flawed.  Any input would be welcome (db attached). Thank you in advance!
Skip SleeperIT ManagerAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
In most Inventory systems, you'd have a "transaction" table which would record any inflow or outflow of items. I don't see where you have this sort of table in your database. I see tables to store Items, Locations, ItemLocations, etc - but I don't see anything to record movement.

For example:

etc etc

So if you ship an item, you'd add a transaction, with a Transaction_Type of "SHIP" (or whatever), and the quantity (perhaps as a negative). If someone returns an item, you'd add another record with a Transaction_Type of "RETURN" and record that as a positive.

If you do a physical stock count, and find that you have 10 more of widget123 than you thought, you would do a transaction with a type of "STOCK_COUNT" with a positive quantity of 10.

Using this method, you can always query for a Date range, and Item, a "type" etc.

You'd also be able to determine historical inventory for a specified range. If you want to include pricing, add a column to the transaction table.
Data design and normalization is THE fundamental skill.
Review the tutorial here
There is no end to the evil that occurs if you try to build forms and reports BEFORE you have a sound data design.
Skip SleeperIT ManagerAuthor Commented:
Hi Nick67,

I believe the db to be normalized and related correctly.  What I don't know if the overall structure and the relationships I've created are correct.

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Skip SleeperIT ManagerAuthor Commented:
Hi Scott,
Thanks for taking a look at my db!  This is strictly for stock tracking with no sales component.   Would the transaction table be considered a junction table?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sales would be an entirely different matter. I included the comment on Pricing since you often want to know what you charged for a specific item for a specific transaction. If your goal is strictly to manage inventory (i.e. stock counts) then you most likely don't need to store pricing. However, don't be surprised when the boss asks your for valuation and stock costing (which would be impossible to determine if you're not storing that information).

Stock transactions have a loose relationship with Sales transactions. You can "sell" something without actually creating a stock movement (generally that's done with a shipping transaction). It's not uncommon for larger companies to have sales orders dated several days/weeks/months out. There is no record of a stock movement associated with those sales until the items are delivered to the customer (ie. "shipped" in some manner) - so until you ship them, they're still in your inventory, regardless of what your sales staff says.

Often an inventory system will track "pending" shipments as well, to determine how much inventory demand you have for a specified time frame. So if you have 100 items in stock, and you have firm sales orders for 50 of those items, and enter sales orders for 75 more, the system will warn you of the shortfall and give you time to make adjustments.

So Sales are tied into DEMAND fulfillment, but not really into inventory, except in a very loose manner.

Would the transaction table be considered a junction table?
That depends on your definition of a Junction Table. The transaction table's role is to record all stock inflow and outflow so you can determine your inventory levels at a specific point-in-time. It does depend on other tables, of course. For example, you most likely have a table named "Materials" or "Inventory" that store all the items you sell. A line in your Transaction table would very likely be related to a record in that table. The Transaction table may also store relationships between other entities. For example, in a "SHIP" transaction, there may be a Sales Order associated with that, so the Transaction table may store the ID value of that Sales Order (or more likely, the Sales Order Line). In a "MATERIAL ISSUE" transaction (i.e. where a Raw Material is issued to a Job, for manufacturing companies) you mist instead store a relationship to a StockItem table, or something of that nature.

As you can see, Inventory Control is a very, very complex thing. It's best to sit down and make a very good flow diagram on exactly how your inventory will move, and use that when creating your structure.
Skip SleeperIT ManagerAuthor Commented:
Hi Scott,

You're correct in that we need to know what our inventory costs are at a specific point in time.  That's where I keep finding problems in building forms.  It seems to me that the costs have to be kept in separate table that is somehow linked/related to the item AND two dates: 1) the date the physical inventory is taken and 2) the date of the last price update.  Can you tell from my design if I'm on the right track?  Thank you so much for you thoughtful insights; I agree that this seems almost a specialty within database design!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Stock valuation is generally a "point in time" value, and unless you store all the data needed to generate that value, you won't be able to recreate it. How you do that depends on what sort of inventory you're dealing with, but in general a transaction table along with a "cost" table would allow you to create the value of your inventory at any point.

The transaction table would be able to give you a relatively accurate count of the items in stock at any point in time.

The cost table could give you the price/cost of each of those items at any point in time.

So from there, you could always calculate stock valuation for a specific point in time.

I'd think your flow would look like this:

1. Do a physical stock count of all inventory items. Enter a line in the Transaction table for those values, and mark them as   a type "BEGBAL"
2. Insert a line in the costing table for each inventory item, and mark it for the current date.

From there, you could join between those tables, and determine your current inventory valuation.

As you move items, you would insert records into the transaction table. In general, outflow items (i.e. shipments to customers) would be entered as negative quantities and inflow items (items created, purchased or returned) would be listed as positive quantities.

Assuming no price changes, you could then create a stock valuation for any point in that flow by querying the transaction table for each distinct material, and then grabbing the associate cost/price for that material from the Costing table.

If the Price does change, then you'd have to query the transaction table for each "segment" of the pricing timeline. For example, if I have material 123, and the price on 01-01-2014 was 5.00, but the price changed on 04-01-2014 to $6.00, then I'd have to query the transaction table for all transaction between 01-01-2014 and 04-01-2014, and multiple that quantity by 5. Any transactions after 04-01-2014 would be multiplied by 6.
Skip SleeperIT ManagerAuthor Commented:
OK, thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.