 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / June 2007Inventory DB design help
I have developed a (what used to be) simple inventory control database to replace the old Cardex system the buyers in my company use to track product inventory. Here is what I have so far… tblInvTransactions TransID TranTypeID (FactoryCount, Deduction, Return, etc- from tblTransType) ProductID POID QtyOrdered QtyOut QtyAdjusted QtyCounted QtyIn BuildingID tblPurchaseOrders POID PONumber SupplierID EmplyeeID ShipViaID StausID POMemo I have very simple forms used to view and manually add transactions into the InvTransactions table. Here is my problem… I would like to be able to have tables that relate in the following way to track PO Received Goods. Each PO could have many products Each Product could have many release Qtys and Dates Each Release could have many receipt Qtys and Dates And now my simple DB has gotten very complex. I have tried numerous times to design the tables and relationships needed but I keep getting hung up on the qtys in the additional tables get “back into?” the Inv Trans table. Could one of you smart people get me on the right track? Thanks, Diane Any inventory tracking system is, inherently, going to be pretty complicated. You won't be able to track everything in a single invTransactions table. Further, when you attempt to merge it with a Purchasing system, you add another layer of complexity to the system. You also cannot track purchase orders in a single PurchaseOrder table. You will need to have a POLines table for each line on the PO and, depending on how detailed you need to get, a POShipments table (which tracks possible multiple shipments for a single line) and a PODistributionsTable (which tracks distribution of costs to multiple cost centers in your company). The last 2 may not be necessary, but are good to keep in mind just in case.  SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com >I have developed a (what used to be) simple inventory control database to > replace the old Cardex system the buyers in my company use to track [quoted text clipped - 45 lines] > Thanks, > Diane Thanks for the confirmation...that is where I was headed...here is what I have got now in addition to my initial PO table: tblPODetails PODetailID POID ProductID OrderQty tblPOReleases POReleaseID PODetailID ReleaseQty RequiredDate PromiseDate tblPOReceipts POReleaseID POShipID ReceivedQty ReceivedDate I am still struggling with how to make this all "work" now. Am I correct only including ProductID in my POdetails table? or should I put it in all of them? I am guessing that my QtyOrdered in my InvTrans table is now not going to exist (it will now be a calucated field in my forms and reports that totals the ReleaseQtys for each PODetail...right?). and somehow I am going to have to make the QtyReceived add to the QtyIn in the transaction table...or will that be a calculated field as well? I was trying to keep all of the transactions in one table so that I could display all of the transactions for one product together in Chronological order. Surely I will still be able to do that with this design, but how? Sorry this is so much, but for some reason I just can't wrap my head around how this is all going to tie together. I will keep reading, doing, and redoing....fortunately most of this is just being done for the learning experience... Thanks again for your help, Diane > Any inventory tracking system is, inherently, going to be pretty > complicated. You won't be able to track everything in a single [quoted text clipped - 56 lines] > > Thanks, > > Diane > tblPODetails > PODetailID [quoted text clipped - 8 lines] > RequiredDate > PromiseDate I'm not quite sure what you have in mind as a Release. In most purchasing systems, that would represent a detail line for a Planned Purchase order, in which the release would be sent to the vendor to order a partial order from the full quantity on the planned order. Is that what you have in mind. > tblPOReceipts > POReleaseID [quoted text clipped - 7 lines] > put > it in all of them? That will depend on more than one factor, but for your situation, I would say just the one table. > I am guessing that my QtyOrdered in my InvTrans table is now not going to > exist (it will now be a calucated field in my forms and reports that [quoted text clipped - 3 lines] > and somehow I am going to have to make the QtyReceived add to the QtyIn in > the transaction table...or will that be a calculated field as well? You need to readdress the multiple quantity fields in your InvTransactions table. You really only need one quantity field, in most cases, and what happens to that quantity would be determined by what KIND of transaction it is. For a receipt, quantity on hand would increase. For an issue out of stores, quantity on hand would decrease, and so on. > I was trying to keep all of the transactions in one table so that I could > display all of the transactions for one product together in Chronological > order. Surely I will still be able to do that with this design, but how? If your tables are properly normalized, you will have no problem selecting them in chronological order. > Sorry this is so much, but for some reason I just can't wrap my head > around [quoted text clipped - 6 lines] > Thanks again for your help, > Diane SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com > You need to readdress the multiple quantity fields in your InvTransactions > table. You really only need one quantity field, in most cases, and what > happens to that quantity would be determined by what KIND of transaction it > is. For a receipt, quantity on hand would increase. For an issue out of > stores, quantity on hand would decrease, and so on. Funny...now I am where I started three weeks ago. One table for every transaction type...each with its own TransID, ProductID and Qty that was where I got stuck and changed to a master transaction table housing all of the transaction info...but I think you are offering me a comprimise... So I revert to the multiple tables I had (one for deductions, one for returns, one for factory count adjustments, and one for POs (actually a few for POs as discussed earlier) And I change my tblInvTrans InvTransID ProductID TransType TransQty and I will use this to calculate my QOH...right? but...how is this new inventory transaction table linked to my other tables that house my actual transactions? In other words, when I recieve goods in how does that qty get into the InvTransaction table? I'm not quite sure what you have in mind as a Release. In most purchasing > systems, that would represent a detail line for a Planned Purchase order, in > which the release would be sent to the vendor to order a partial order from > the full quantity on the planned order. Is that what you have in mind. Similar....the buyers place one order, with one POnumber, on one date....but, the PO may say 50 of part A to be shipped 10 a month for the next 5 months. Those are what they call releases. Thanks again for all of your help... Diane > > tblPODetails > > PODetailID [quoted text clipped - 60 lines] > > Thanks again for your help, > > Diane > returns, one for factory count adjustments, and one for POs (actually a > few [quoted text clipped - 13 lines] > in > how does that qty get into the InvTransaction table? This is where the fun begins. You will have to write VBA code to update yoru InvTransaction table when transactions are entered into the other tables. Thus, when a product is received into the receiving tables, you will need to update that quantity in the transactions table. > Similar....the buyers place one order, with one POnumber, on one > date....but, the PO may say 50 of part A to be shipped 10 a month for the > next 5 months. Those are what they call releases. Excellent. That is exactly what I meant by a release too. SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com >> You need to readdress the multiple quantity fields in your >> InvTransactions [quoted text clipped - 124 lines] >> > Thanks again for your help, >> > Diane >This is where the fun begins. You will have to write VBA code to update yoru >InvTransaction table when transactions are entered into the other tables. >Thus, when a product is received into the receiving tables, you will need to >update that quantity in the transactions table. So I was on the right track all along. No wonder I got stuck...my VBA knowledge is limited...I have done very little code writing from scratch, only copied, or tried to fix others code. But I have been learning it slowly, now is the time to break out my books I have been ignoring. One last question before I go off to teach myself VBA... In the IVTrans table... My VBA will update the ProductID, TransQty, and TransType all from my other tables...Right? Can the InvTransID be updated like the TransQty as well..for example if my ReceiptID is R5000 (autonumber) then can that also be my InvTransID? or does it have to be another unique number? Also, in my table relationships window (I need the visual) will my InvTrans table only be linked to my Products table and my TransactionTypes table for RI? Thanks so much for all of your help... Diane > > returns, one for factory count adjustments, and one for POs (actually a > > few [quoted text clipped - 153 lines] > >> > Thanks again for your help, > >> > Diane Hi You can find a useful POINT of Sales MS Access Application Fully working to Download here http://access-forum.com/plus/viewtopic.php?t=205 Hope it helps Ch.Yallin diaare : > >This is where the fun begins. You will have to write VBA code to update yoru > >InvTransaction table when transactions are entered into the other tables. [quoted text clipped - 192 lines] > > >> Microsoft MVP (Access) > > >> www.ltcomputerdesigns.com > My VBA will update the ProductID, TransQty, and TransType all from my > other > tables...Right? Yes > Can the InvTransID be updated like the TransQty as well..for example if my > ReceiptID is R5000 (autonumber) then can that also be my InvTransID? or > does > it have to be another unique number? If I understand your situation right, the InvTransID is a unique value for each transaction that occurs and will not be updatable. I'm not sure quite how you have an "autonumber" value of 'R5000'. SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com > If I understand your situation right, the InvTransID is a unique value for > each transaction that occurs and will not be updatable. I'm not sure quite > how you have an "autonumber" value of 'R5000'. For each of my seperate tables (deductions, factorycounts, receipts, etc) I have my PK set as an autonumber preceded by a letter ("R"&[autonumber] for receipts, "D"&[autonumber] for deductions, etc). I was hoping to use these same numbers as my InvTransID's. They would each be unique due to the letters preceding the autonumbers. That way when I look at the PK feild in the InvTrans table I could tell at a quick glance what type of transaction they were from the InvTransID. Not sure it makes that much of a difference, it was just a thought. Thanks again for all of your help, Diane > > My VBA will update the ProductID, TransQty, and TransType all from my > > other [quoted text clipped - 10 lines] > each transaction that occurs and will not be updatable. I'm not sure quite > how you have an "autonumber" value of 'R5000'. > For each of my seperate tables (deductions, factorycounts, receipts, etc) > I [quoted text clipped - 8 lines] > > Not sure it makes that much of a difference, it was just a thought. So you are concatenating a letter to the autonumber field. That's just fine for display purposes. You should probably not use those values as the transaction ids. You are asking for trouble if you do that. SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com Lynn Thanks so much for all of your advice...I have learned tons since we started this conversation. Here is my next stumbling block.... In my InvTrans table I have an InvTransID [autonumber] set as my PK. In addition, I would really like to include in my InvTransactions table a feild which houses the ID (autonumber FK) of the table from which the data came. In other words...if a product was received in my ReceivedGoods table and was assigned a ReceiptID, I would like that too to be added to the InvTrasactions table...so I can refer back to where that line got added from. I would like all of these FK's to be appended into the same feild (TransID) in the InvTrans table. My problem is that this results in Key violations, the number 5 could be assigned to the deductionsID and the receivedGoodsID etc.... I know one way to work around this is to have each FK ID feed a different feild, but is that the only way? Thanks, Diane > I would like all of these FK's to be appended into the same feild > (TransID) [quoted text clipped - 4 lines] > I know one way to work around this is to have each FK ID feed a different > feild, but is that the only way? Do you have a unique index set on the TransID field? If so, you need to remove that. SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com After further thought this is where I am at... I went ahead and had each transID (deductID, recievedID, FCID, etc) go into its own field in the InvTrans Table and have this feild set at Indexed(no duplicates). This way, only new records will be added to the InvTrans table due to key violations (Good). But, what if the user edits a line in the form linked to the deductions table? Say they recounted and relalized that the first qty entered was wrong. This edit will be made on a record that already has been added to the InvTrans table...so now my key violation wont let it be added again (good, but it also won't allow my new Qty to be changed..bad). How do I get it InvTrans table to update any changes made to records already in the table. In other words, I changed the Qty in a record in the factorycount table, how do I get the same change to be made in the InvTrans table at the same time... Thanks again for your time and help. Diane > > I would like all of these FK's to be appended into the same feild > > (TransID) [quoted text clipped - 7 lines] > Do you have a unique index set on the TransID field? If so, you need to > remove that. Diane, In most inventory systems, you would never do an update to a TransactionsTable record. Rather, you would enter a new record as an adjustment. SignatureLynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com > After further thought this is where I am at... > [quoted text clipped - 36 lines] >> Do you have a unique index set on the TransID field? If so, you need to >> remove that. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.