If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Inventory database
I am looking to create a database which will keep track of a warehouse
inventory as well as inventory on sales trucks and track customer sales. Want to link tables so that totals are linked when inventory is transferred to trucks the warehouse inventory reflects this transfer. Also truck inventory to reflect custormer sales. Any suggestions as to what type of tables, forms, querys and relationships I should create would be helpfull. Thank you in advance for your help. |
#2
|
|||
|
|||
Inventory database
On Mon, 06 Oct 2008 16:12:09 GMT, "pkfloyd" u46723@uwe wrote:
Assuming you are using Access 2007, there is an Inventory template. File New Business. For older versions you may also find templates at office.microsoft.com. "Totals are linked"? That sounds like you want to store totals in the db. If these are totals that can be calculated, it is likely better to calculate them on the fly in a query. -Tom. Microsoft Access MVP I am looking to create a database which will keep track of a warehouse inventory as well as inventory on sales trucks and track customer sales. Want to link tables so that totals are linked when inventory is transferred to trucks the warehouse inventory reflects this transfer. Also truck inventory to reflect custormer sales. Any suggestions as to what type of tables, forms, querys and relationships I should create would be helpfull. Thank you in advance for your help. |
#3
|
|||
|
|||
Inventory database
You are asking about a transaction based inventory system. The Northwind example does not do this. Below is a summary on this that I previously wrote. Sincerely, Fred Most people would define inventory tracking is an information system that always “knows” the current inventory. Automated inventory systems handle this by automatically adjusting the inventory numbers for the various happenings which increase or decrease inventory. For those that don’t already have this in place, this is a 10 times larger job than they think it is. So, for many of them, a “less perfect” system that doesn’t fully do this would be better for them. For example, where you update the inventory figure by simply entering in then new total count for that item. If not, read on! The central “information” system can be paper/card or electronic. Electronic systems can be of various types such as text documents, spreadsheets, but most are database based. For this example Here are the main steps.....always read the later ones before you start.....where you are headed should always influence what you do. 1. Create unique “names” / identifiers for all items that you want to track. The most common example of this is your company’s part number for the system, following all of the rules for a good part numbering system. An alternative is somebody else’s part number combined with their name. For wording in this writing, I will presume you’re using “part number” This includes defining all units of measure. . E.G. does a part number for rope mean 1 foot of that rope, one 200’ spool of that rope etc. 2. Define your “sphere” of what will be considered to be “in” your inventory. Is this your stockroom, your whole building, your whole company, a single service truck? 3. List all of the current ways in your company that inventory of an item in your “sphere” could be modified*. The 4 main categories of this a Income Outgo (sales, consumption etc.) Creation* Destruction* * E.G. If, by a manufacturing act, you use part a and part b to build part c, that act “destroys” A & B and “creates” C The results must be 4. Set up and implement procedures, rules, practices to make sure that every instance of every item #3 will get recorded as a transaction for each part number involved, and that happenings not under #3 are not recorded.* Usually, this requires defining a mental or physical around your “sphere”. For example, if your “sphere” is (only) your stockroom and your production area, then your procedures must make sure that moving an item from your stockroom to your production area is never recorded as a transaction, and moving an item from either of those two areas to elsewhere is always recorded as a transaction. 5. Get / Set up a data system which has a current inventory quantity for each part number, and which supports receiving each recorded transaction and making the appropriate modifications to inventory quantities for each recorded transaction.* 6. Make sure each transaction gets processed in the data system.* Note: A portion of the items under #3 (and processed under #4, #5 & #6) might be, or are set up as happenings within the same data system. Examples might be sales, shipments, usage on work orders, instances of manufacturing, “receiving” etc.. In these cases, decide which of these your data system can and will interpret and execute as inventory transactions, and get/make a data system that properly does so. Then, when implementing your procedures under #4, consider entry/execution of such an action in the data system to also be recording of the inventory transaction I am looking to create a database which will keep track of a warehouse inventory as well as inventory on sales trucks and track customer sales. Want to link tables so that totals are linked when inventory is transferred to trucks the warehouse inventory reflects this transfer. Also truck inventory to reflect custormer sales. Any suggestions as to what type of tables, forms, querys and relationships I should create would be helpfull. Thank you in advance for your help. |
#4
|
|||
|
|||
Inventory database
Great information, Fred. There are a couple of more advanced topics that
could be added. That would be serialized inventory and kit inventory, and substitution items. Serialized inventory is where some or all inventory items have an individual serial number. Kit inventory is where an item may be contstructed from other inventory items or may be deconstructed to supply requirements from its component parts. Substitution inventory is where item QAZ can be substitued for THB. But, those are very advanced inventory considerations. "Fred" wrote in message ... You are asking about a transaction based inventory system. The Northwind example does not do this. Below is a summary on this that I previously wrote. Sincerely, Fred Most people would define inventory tracking is an information system that always "knows" the current inventory. Automated inventory systems handle this by automatically adjusting the inventory numbers for the various happenings which increase or decrease inventory. For those that don't already have this in place, this is a 10 times larger job than they think it is. So, for many of them, a "less perfect" system that doesn't fully do this would be better for them. For example, where you update the inventory figure by simply entering in then new total count for that item. If not, read on! The central "information" system can be paper/card or electronic. Electronic systems can be of various types such as text documents, spreadsheets, but most are database based. For this example Here are the main steps.....always read the later ones before you start.....where you are headed should always influence what you do. 1. Create unique "names" / identifiers for all items that you want to track. The most common example of this is your company's part number for the system, following all of the rules for a good part numbering system. An alternative is somebody else's part number combined with their name. For wording in this writing, I will presume you're using "part number" This includes defining all units of measure. . E.G. does a part number for rope mean 1 foot of that rope, one 200' spool of that rope etc. 2. Define your "sphere" of what will be considered to be "in" your inventory. Is this your stockroom, your whole building, your whole company, a single service truck? 3. List all of the current ways in your company that inventory of an item in your "sphere" could be modified*. The 4 main categories of this a Income Outgo (sales, consumption etc.) Creation* Destruction* * E.G. If, by a manufacturing act, you use part a and part b to build part c, that act "destroys" A & B and "creates" C The results must be 4. Set up and implement procedures, rules, practices to make sure that every instance of every item #3 will get recorded as a transaction for each part number involved, and that happenings not under #3 are not recorded.* Usually, this requires defining a mental or physical around your "sphere". For example, if your "sphere" is (only) your stockroom and your production area, then your procedures must make sure that moving an item from your stockroom to your production area is never recorded as a transaction, and moving an item from either of those two areas to elsewhere is always recorded as a transaction. 5. Get / Set up a data system which has a current inventory quantity for each part number, and which supports receiving each recorded transaction and making the appropriate modifications to inventory quantities for each recorded transaction.* 6. Make sure each transaction gets processed in the data system.* Note: A portion of the items under #3 (and processed under #4, #5 & #6) might be, or are set up as happenings within the same data system. Examples might be sales, shipments, usage on work orders, instances of manufacturing, "receiving" etc.. In these cases, decide which of these your data system can and will interpret and execute as inventory transactions, and get/make a data system that properly does so. Then, when implementing your procedures under #4, consider entry/execution of such an action in the data system to also be recording of the inventory transaction I am looking to create a database which will keep track of a warehouse inventory as well as inventory on sales trucks and track customer sales. Want to link tables so that totals are linked when inventory is transferred to trucks the warehouse inventory reflects this transfer. Also truck inventory to reflect custormer sales. Any suggestions as to what type of tables, forms, querys and relationships I should create would be helpfull. Thank you in advance for your help. |
#5
|
|||
|
|||
Inventory database
Yeah, you're right.
My work has always been in manufacturing where kitting and substitutions are subsets of / already covered by manufacturing (= consuming items to "make" items) and so I underemphasized and overgeneralized those in my post. And I never had to do automated inventory where the serial number sof the individual items had to be recorded / tracked. Sincerely, Fred |
#6
|
|||
|
|||
Inventory database
Back in the dark ages of mini computers using a really good version of BASIC
called Business Basic, I did an Inventory tracking system for a gun dealer. Tracking serial numbers was very important. Had to be able to track an item from the time it hit the door until the time it left the store. "Fred" wrote in message ... Yeah, you're right. My work has always been in manufacturing where kitting and substitutions are subsets of / already covered by manufacturing (= consuming items to "make" items) and so I underemphasized and overgeneralized those in my post. And I never had to do automated inventory where the serial number sof the individual items had to be recorded / tracked. Sincerely, Fred |
#7
|
|||
|
|||
Inventory database
Hi
There are some great designs for Inventories (and loads of other treasures) here. http://www.databaseanswers.org/data_models/index.htm Can they be adapted for your purpose? Evi "pkfloyd" u46723@uwe wrote in message news:8b4487f57710d@uwe... I am looking to create a database which will keep track of a warehouse inventory as well as inventory on sales trucks and track customer sales. Want to link tables so that totals are linked when inventory is transferred to trucks the warehouse inventory reflects this transfer. Also truck inventory to reflect custormer sales. Any suggestions as to what type of tables, forms, querys and relationships I should create would be helpfull. Thank you in advance for your help. |
Thread Tools | |
Display Modes | |
|
|