Skip Sleeper
asked on
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!
HKL-Inventory.accdb
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!
HKL-Inventory.accdb
ASKER
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.
Thanks!
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.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
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.
ASKER
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!
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!
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.
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.
ASKER
OK, thanks!
Review the tutorial here
http://phlonx.com/resources/nf3/
There is no end to the evil that occurs if you try to build forms and reports BEFORE you have a sound data design.