access 2010 relationships

Posted on 2014-12-30
Last Modified: 2014-12-31
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!
Question by:fargus47
  • 4
  • 3
LVL 26

Expert Comment

Comment Utility
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.

Author Comment

Comment Utility
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.

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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.

Author Comment

Comment Utility
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?
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

LVL 84
Comment Utility
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.

Author Comment

Comment Utility
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!
LVL 84
Comment Utility
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.

Author Comment

Comment Utility
OK, thanks!

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now