Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


access 2010 relationships

Posted on 2014-12-30
Medium Priority
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:Skip Sleeper
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 26

Expert Comment

ID: 40523860
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

by:Skip Sleeper
ID: 40523870
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40524117
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Skip Sleeper
ID: 40524329
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?
LVL 85
ID: 40525287
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

by:Skip Sleeper
ID: 40525408
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 85
ID: 40525432
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

by:Skip Sleeper
ID: 40525435
OK, thanks!

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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