By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,606 Members | 3,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,606 IT Pros & Developers. It's quick & easy.

Inventory Form that adds/subtracts from current inventory number in table

P: 39
I am currently trying to create an inventory database that includes a form that will update the inventory numbers last updated in the database. Rather than having one row constantly being updated in the table, I would like to keep track of what has come in and out of the warehouse and when.

So far, I have the fields on my form and am able to update the next record by entering in numbers, but I'm not sure how to use what I enter into the fields (based on a dropdown of inbound/outbound) to either subtract from or add to the last updated record in the table.

Does this make sense? And can anyone help me learn how to do this?
Apr 4 '11 #1

✓ answered by NeoPa

Databases work a little differently from how you're used to I expect. That bit you didn't follow was fundamental to the difference and explains why your follow-on questions doesn't really work. Let me see if I can clarify the situation somewhat.

Take the following tables as a basis :
[tblProduct]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. pProductID     AutoNumber
  3. pProductName   Text
[tblLocation]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. lLocationID    AutoNumber
  3. lLocationName  Text
[tblStock]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. sStockID       AutoNumber
  3. sProductID     Number
  4. sLocationID    Number
[tblTransaction]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. tTranID        AutoNumber
  3. tStockID       Number
  4. tTranDate      DateTime
  5. tStockAdjust   Number
We'll start with a single product and a single location, which leaves us a single Stock record, then look at the Transactions afterwards and how we use those to maintain and view the results.

[tblProduct]
Expand|Select|Wrap|Line Numbers
  1. pProductID = 1, pProductName = Pen
[tblLocation]
Expand|Select|Wrap|Line Numbers
  1. lLocationID = 1, lLocationName = London
[tblStock]
Expand|Select|Wrap|Line Numbers
  1. sStockID = 1, sProductID = 1, sLocationID = 1
[tblTransaction]
Expand|Select|Wrap|Line Numbers
  1. tTranID = 1, tStockID = 1, tTranDate = 1 Jan 2011, tStockAdjust = 100
  2. tTranID = 2, tStockID = 1, tTranDate = 1 Jan 2011, tStockAdjust = -10
  3. tTranID = 3, tStockID = 1, tTranDate = 1 Feb 2011, tStockAdjust = -5
  4. tTranID = 4, tStockID = 1, tTranDate = 1 Mar 2011, tStockAdjust = -35
  5. tTranID = 5, tStockID = 1, tTranDate = 1 Apr 2011, tStockAdjust = -10
  6. tTranID = 6, tStockID = 1, tTranDate = 1 May 2011, tStockAdjust = -5
  7. tTranID = 7, tStockID = 1, tTranDate = 1 Jun 2011, tStockAdjust = -5
  8. tTranID = 8, tStockID = 1, tTranDate = 1 Jul 2011, tStockAdjust = -2
  9. tTranID = 9, tStockID = 1, tTranDate = 1 Aug 2011, tStockAdjust = -1
We can see from the data that 100 Pens were put into stock in January, but that over the next eight months a total of 73 Pens were removed from stock, leaving a balance of 27. The thing to note is that 27 is not stored anywhere. Instead, a report on the stock would sum the values together of all the related transactions and provide the result on the fly. Such a report might be built on a query, the SQL of which is below as an example :

Expand|Select|Wrap|Line Numbers
  1. SELECT   tL.lLocationName
  2.        , tP.pProductName
  3.        , Sum(tT.tStockAdjust) AS StockLevel
  4.  
  5. FROM   ((tblStock AS tS
  6.          INNER JOIN
  7.          tblLocation AS tL
  8.   ON     tS.sLocationID = tL.lLocationID)
  9.          INNER JOIN
  10.          tblProduct AS tP
  11.   ON     tS.sProductID = tP.pProductID)
  12.          INNER JOIN
  13.          tblTransaction AS tT
  14.   ON     tS.sStockID = tT.tStockID
  15.  
  16. GROUP BY tL.lLocationID
  17.        , tP.pProductID
  18.  
  19. ORDER BY tL.lLocationName
  20.        , tP.pProductName
The result would be :
Expand|Select|Wrap|Line Numbers
  1. London, Pen, 27
That's the concept. In some situations it's deemed politic to store dated values in a Stock level table, but in such cases great care must be taken to recognise which transactions are already incorporated into the total (hence the Stock value needs to be dated).

Does that make sense?

PS. Trying to update the data in the Stock table is not a normalised approach, and that can bring with it many unforeseen difficulties. For more on this see Database Normalisation and Table structures.

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,494
It doesn't make a lot of sense to be fair Amy, but I think I have a sort of understanding of where you're going with it (Forms don't have fields by the way. They have controls. Understanding this may save you future headaches).

Are you thinking about maintaining a transaction table rather than simply updating the inventory records directly?

Things can work that way, but the results would not generally be stored (The inventory wouldn't be a stored value as such). Instead, the item would be linked in a query to all the related transaction records, and the sums resulting would be the values displayed indicating the stock levels. Does that make sense?
Apr 4 '11 #2

P: 39
Yes, that makes sense. Thank you for being gracious with my limited knowledge. A transaction table does sound more like what I need.

I don't understand: "Instead, the item would be linked in a query to all the related transaction records, and the sums resulting would be the values displayed indicating the stock levels."

What I need to know is how to create a new record on a table based on the last updated transaction record and the values entered into the controls on the form.
Apr 4 '11 #3

NeoPa
Expert Mod 15k+
P: 31,494
Databases work a little differently from how you're used to I expect. That bit you didn't follow was fundamental to the difference and explains why your follow-on questions doesn't really work. Let me see if I can clarify the situation somewhat.

Take the following tables as a basis :
[tblProduct]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. pProductID     AutoNumber
  3. pProductName   Text
[tblLocation]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. lLocationID    AutoNumber
  3. lLocationName  Text
[tblStock]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. sStockID       AutoNumber
  3. sProductID     Number
  4. sLocationID    Number
[tblTransaction]
Expand|Select|Wrap|Line Numbers
  1. Field          Type
  2. tTranID        AutoNumber
  3. tStockID       Number
  4. tTranDate      DateTime
  5. tStockAdjust   Number
We'll start with a single product and a single location, which leaves us a single Stock record, then look at the Transactions afterwards and how we use those to maintain and view the results.

[tblProduct]
Expand|Select|Wrap|Line Numbers
  1. pProductID = 1, pProductName = Pen
[tblLocation]
Expand|Select|Wrap|Line Numbers
  1. lLocationID = 1, lLocationName = London
[tblStock]
Expand|Select|Wrap|Line Numbers
  1. sStockID = 1, sProductID = 1, sLocationID = 1
[tblTransaction]
Expand|Select|Wrap|Line Numbers
  1. tTranID = 1, tStockID = 1, tTranDate = 1 Jan 2011, tStockAdjust = 100
  2. tTranID = 2, tStockID = 1, tTranDate = 1 Jan 2011, tStockAdjust = -10
  3. tTranID = 3, tStockID = 1, tTranDate = 1 Feb 2011, tStockAdjust = -5
  4. tTranID = 4, tStockID = 1, tTranDate = 1 Mar 2011, tStockAdjust = -35
  5. tTranID = 5, tStockID = 1, tTranDate = 1 Apr 2011, tStockAdjust = -10
  6. tTranID = 6, tStockID = 1, tTranDate = 1 May 2011, tStockAdjust = -5
  7. tTranID = 7, tStockID = 1, tTranDate = 1 Jun 2011, tStockAdjust = -5
  8. tTranID = 8, tStockID = 1, tTranDate = 1 Jul 2011, tStockAdjust = -2
  9. tTranID = 9, tStockID = 1, tTranDate = 1 Aug 2011, tStockAdjust = -1
We can see from the data that 100 Pens were put into stock in January, but that over the next eight months a total of 73 Pens were removed from stock, leaving a balance of 27. The thing to note is that 27 is not stored anywhere. Instead, a report on the stock would sum the values together of all the related transactions and provide the result on the fly. Such a report might be built on a query, the SQL of which is below as an example :

Expand|Select|Wrap|Line Numbers
  1. SELECT   tL.lLocationName
  2.        , tP.pProductName
  3.        , Sum(tT.tStockAdjust) AS StockLevel
  4.  
  5. FROM   ((tblStock AS tS
  6.          INNER JOIN
  7.          tblLocation AS tL
  8.   ON     tS.sLocationID = tL.lLocationID)
  9.          INNER JOIN
  10.          tblProduct AS tP
  11.   ON     tS.sProductID = tP.pProductID)
  12.          INNER JOIN
  13.          tblTransaction AS tT
  14.   ON     tS.sStockID = tT.tStockID
  15.  
  16. GROUP BY tL.lLocationID
  17.        , tP.pProductID
  18.  
  19. ORDER BY tL.lLocationName
  20.        , tP.pProductName
The result would be :
Expand|Select|Wrap|Line Numbers
  1. London, Pen, 27
That's the concept. In some situations it's deemed politic to store dated values in a Stock level table, but in such cases great care must be taken to recognise which transactions are already incorporated into the total (hence the Stock value needs to be dated).

Does that make sense?

PS. Trying to update the data in the Stock table is not a normalised approach, and that can bring with it many unforeseen difficulties. For more on this see Database Normalisation and Table structures.
Apr 5 '11 #4

P: 39
This makes much more sense, thank you.

Another question, I would like to create a form that allows users to enter in changes in transactions. Is there anyway the number they enter can automatically have a negative sign come before it? Or could I base the fact that there is a negative on the number on whether a drop-down box control has "inbound" or "outbound" selected?
Apr 5 '11 #5

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome Amy.

As for the new question, such things can be done, but this needs to be posted in its own thread. Only one question per thread please.
Apr 7 '11 #6

NeoPa
Expert Mod 15k+
P: 31,494
I didn't point it out earlier, but for stock-take, where you go around your warehouse and check that the counts of each item matches the counts indicated by your system, you would enter only the deltas when you found anything that didn't match. Delta is a mathematical name (Greek letter.) for a difference. So, if an item's 3 short of the stock quantity indicated, then you'd add an adjustment of +3. If you actually have five more than expected you'd enter -5.

As a general rule you'd want to add a comment to the transaction to indicate this is from a stock-take. For this you'd need an extra field in [tblTransaction] called either [Comment] or [Reference] and you'd populate it with something meaningful when ordinary sale and purchase transactions are used.
Aug 26 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.