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

**NeoPa** · Apr 4 '11, 11:25 PM

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?

**Amy Badgett** · Apr 4 '11, 11:33 PM

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.

**NeoPa** · Apr 5 '11, 12:15 AM

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]

Code:

[B][U]Field[/U]          [U]Type[/U][/B]
pProductID     AutoNumber
pProductName   Text

[tblLocation]

Code:

[B][U]Field[/U]          [U]Type[/U][/B]
lLocationID    AutoNumber
lLocationName  Text

[tblStock]

Code:

[B][U]Field[/U]          [U]Type[/U][/B]
sStockID       AutoNumber
sProductID     Number
sLocationID    Number

[tblTransaction]

Code:

[B][U]Field[/U]          [U]Type[/U][/B]
tTranID        AutoNumber
tStockID       Number
tTranDate      DateTime
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]

Code:

pProductID = 1, pProductName = Pen

[tblLocation]

Code:

lLocationID = 1, lLocationName = London

[tblStock]

Code:

sStockID = 1, sProductID = 1, sLocationID = 1

[tblTransaction]

Code:

tTranID = 1, tStockID = 1, tTranDate = 1 Jan 2011, tStockAdjust = 100
tTranID = 2, tStockID = 1, tTranDate = 1 Jan 2011, tStockAdjust = -10
tTranID = 3, tStockID = 1, tTranDate = 1 Feb 2011, tStockAdjust = -5
tTranID = 4, tStockID = 1, tTranDate = 1 Mar 2011, tStockAdjust = -35
tTranID = 5, tStockID = 1, tTranDate = 1 Apr 2011, tStockAdjust = -10
tTranID = 6, tStockID = 1, tTranDate = 1 May 2011, tStockAdjust = -5
tTranID = 7, tStockID = 1, tTranDate = 1 Jun 2011, tStockAdjust = -5
tTranID = 8, tStockID = 1, tTranDate = 1 Jul 2011, tStockAdjust = -2
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 :

Code:

SELECT   tL.lLocationName
       , tP.pProductName
       , Sum(tT.tStockAdjust) AS StockLevel

FROM   ((tblStock AS tS
         INNER JOIN
         tblLocation AS tL
  ON     tS.sLocationID = tL.lLocationID)
         INNER JOIN
         tblProduct AS tP
  ON     tS.sProductID = tP.pProductID)
         INNER JOIN
         tblTransaction AS tT
  ON     tS.sStockID = tT.tStockID

GROUP BY tL.lLocationID
       , tP.pProductID

ORDER BY tL.lLocationName
       , tP.pProductName

The result would be :

Code:

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.

**Amy Badgett** · Apr 5 '11, 11:57 PM

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?

**NeoPa** · Apr 7 '11, 03:49 AM

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.

**NeoPa** · Aug 26 '18, 01:48 PM

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.