Solved Stock Transaction Table Advice

[MattBaldry]

Hi all,

I am looking at reworking my MRP database to manage the stock numbers better. At the moment I have a TransactionType field which denotes the transaction type to either In or Out. The TransactionQuantity field is currently always a positive number. I have seen on other DBs that they have this field as a negative number with the Type is Out. I currently have a query to work out QuantityInStock and it needs to use the In/Out value to add or subtract the quantities to get the value.

Would my query be more efficient if the TransactionQuantity already had a minus number when it is an Out transaction? Would I then just need to run a Sum on this field to get my QIS?

~Matt

 

[Gasman]

Yes.
When I was making a payment, I still entered a positive number, and the form made it negative if payment type.
Your issue appears to be the same?

 

[The_Doc_Man]

Depends on lots of factors, but since this kind of work will usually be done by a summation query anyway, you might consider leaving quantity as always positive.

Then when you do your "sum of transactions" query to show current stock balance, you would include

SELECT StockID, ...., SUM( IIF( XActType='IN', XActQty, -XActQty) ) AS SumOfCorrectedQty, ....

Now if you had more than just in/out transactions (i.e. transactions could be "ShipmentIn", "SalesOut", ShrinkageAdjust", "ReturnToStock", "SightInventory", etc.) then you might want a table to supply the "correction constant" of +1 or -1 as appropriate to the direction of the transfer, and in that case would involve a JOIN with the correction constant table, whatever it is. When you have only two choices, the IIF will do the job. If / when it grows more complex, consider the JOIN.

 

[Gasman]

My thoughts on the way I did it, was the change was done once, and that was it, rather than having to calculate any time the value was used?

 

[The_Doc_Man]

Gasman, there is no guarantee that my way is better (or worse). Just different. However, I took the viewpoint that I might have to build a report showing all transactions of a given type or on a given date, and if I modify the permanent record in the table, then I have to decide if I wanted to flip the sign for reports. It's of the "pay me now or pay me later" variety.

 

[Pat Hartman]

I typically have more a lot more than two transaction types and some might be either positive or negative like adjustments. Therefore, in order to allow the user to enter only a positive number, I store a multiplier in the transactionType table. For all positive or variable types, the multiplier is 1. For all negative types the multiplier is -1. So when a user enters an adjustment type transaction, he enters a positive number to increment the quantity or a negative number to decrement it. Multiplying by 1 doesn't affect the value.

Therefore, in the query that sums the transactions, I use

Sum(AMT * Multiplier) As NaturalSum

This also makes it easier if your users like to see Credits and Debits in separate columns all with positive signs.

 

[CJ_London]

over time I've used both methods. For me it comes down to where the data comes from. Either way I always have a transaction type as often there are other factors in play, particularly on presentation. I certainly don't use separate columns for in/out, debit/credit

My view is if the data primarily comes from manual input - better to keep the numbers positive and use a 1/-1 multiplier depending on transaction type. It is easier for the user.

However if data primarily comes from other systems then I might use negative numbers if that is how the source data is presented to make it easier for reconciling values.

Other factors that come into play as to which is better is what you are going to do with the data - for example searching for a value (easier to find 123 rather than 123 AND -123) and sorting (again easier to find 123 on adjacent rows rather than one at the top and the other at the bottom). Easier to display data in multiple columns where both columns are shown as positive. In finance, easier to match debits and credits without an additional calculation.

Summing is a bit more complex because it involves a calculation on each presentation. With negative numbers you only need the calculation when the data is entered or if you then need to display negatives as a positive.

Would my query be more efficient if the TransactionQuantity already had a minus number when it is an Out transaction?

So I think it depends on what works for you. I wouldn't look at the one query for a decision, look at the whole application. But in all my years of developing I have not noticed a performance hit with keeping everything positive. If you are not sure, copy your data to a temporary table, converting the outs to negative and run your query again, see what time difference you get..

As far as your quantity in stock query is concerned, a common mistake is to sum from the first record, that will get slow after time. I expect you have stocktakes, so your starting point should be the date of the last stocktake - I include that as a transaction type even though it is not a transaction per se. The value is always 0 or positive (you can't have negative stock) and once entered there is a rule that prevents users from entering records that predate the stocktake. There is a bit more to it around adjustments required for financial reporting reasons, but you get the idea. You can even run a monthly/quarterly routine to append calculated stocktake values (with a stocktake transaction type of course)

 

[MattBaldry]

Thanks guys. I love reading your views on questions on here. It helps me learn. I think that, as it does work, I will leave it as is for now. I have a separate table, tblTransactionType, where the users can add as many types as they like, and then specify if they are In or Out.

As there doesn't seem to be any real benefit to having negative numbers in the query, I will leave the query to use the In/Out field when making calculations.

~Matt

 

[CJ_London]

there may be a bit of a performance hit if you are interpreting text

iif(inout="IN",1,-1)*quantity

or
iif(inout="IN",quantity,-quantity)

v

inout*quantity

where inout is a number

 

[gemma-the-husky]

one of the best inventory databases I saw was the Dharamsala Tea database which was an MS sample. (Dharamsala Tea was the first product in the items table). The only poor choice was showing inventory movements as all positive. Change it to show sales as negative, and it was a superb database.

if you have to union movements in and out to get a net movement it's a pain as the union queries become non updateable. Having sales as minus makes it work a lot more easily. If you calculate the movement as in the last example, you can't easily amend the quantity

 

[MattBaldry]

there may be a bit of a performance hit if you are interpreting text

iif(inout="IN",1,-1)*quantity

or
iif(inout="IN",quantity,-quantity)

v

inout*quantity

where inout is a number

I see what you mean here. So if I have the settings form to allow the user to select In or Out but the table itself stores the value as 1 or -1, that would work?

~Matt

 

[CJ_London]

yes - just use a combo with a value list of

1;"IN";-1:"OUT"

 

[gemma-the-husky]

over time I've used both methods. For me it comes down to where the data comes from. Either way I always have a transaction type as often there are other factors in play, particularly on presentation. I certainly don't use separate columns for in/out, debit/credit

My view is if the data primarily comes from manual input - better to keep the numbers positive and use a 1/-1 multiplier depending on transaction type. It is easier for the user.

However if data primarily comes from other systems then I might use negative numbers if that is how the source data is presented to make it easier for reconciling values.

Other factors that come into play as to which is better is what you are going to do with the data - for example searching for a value (easier to find 123 rather than 123 AND -123) and sorting (again easier to find 123 on adjacent rows rather than one at the top and the other at the bottom). Easier to display data in multiple columns where both columns are shown as positive. In finance, easier to match debits and credits without an additional calculation.

Summing is a bit more complex because it involves a calculation on each presentation. With negative numbers you only need the calculation when the data is entered or if you then need to display negatives as a positive.


So I think it depends on what works for you. I wouldn't look at the one query for a decision, look at the whole application. But in all my years of developing I have not noticed a performance hit with keeping everything positive. If you are not sure, copy your data to a temporary table, converting the outs to negative and run your query again, see what time difference you get..

As far as your quantity in stock query is concerned, a common mistake is to sum from the first record, that will get slow after time. I expect you have stocktakes, so your starting point should be the date of the last stocktake - I include that as a transaction type even though it is not a transaction per se. The value is always 0 or positive (you can't have negative stock) and once entered there is a rule that prevents users from entering records that predate the stocktake. There is a bit more to it around adjustments required for financial reporting reasons, but you get the idea. You can even run a monthly/quarterly routine to append calculated stocktake values (with a stocktake transaction type of course)

That's a good point about the stock take. A lot of systems will have "stocktakeadjustment" as a data type (which incidentally is another reason to have a signed quantity - since this is one transaction type that can be either positive or negative).

If you don't count every item at a stock take, you still might want to store the (theoretical stock) at the common date, and store this date in a stock count record - as otherwise each product will need it's own date, and evaluating the current stock balance for each inventory item will be more tricky, as each one might start from a different date.

 

[CJ_London]

as otherwise each product will need it's own date, and evaluating the current stock balance for each inventory item will be more tricky, as each one might start from a different date.

they do - I've written stock systems where there is simply too much stock to count in one hit causing significant business interruption - auditors are usually happy with a calculated stocktake, so long as each product is physically counted at least once a year.

I use a query to get the date of the last physical stocktake record for each product then use an unequal join to get that and subsequent transactions. theoretical stocktakes are stored for accounting purposes (defaulting to 0 for negative values) but are otherwise ignored when a physical stocktake is done (for reconciliation activities). Records are timestamped so easy to identify records created after the stocktake date but have a predated transaction date.

As part of the stocktake process stocktakers are provided with a sheet of products and their theoretical stock so any discrepancies can be quickly identified and investigated.

There are a number of things to consider - stock arrives and goes out, But the 'out' record' is created before the 'in record' so you potentially have negative stock between these two entries.

 

[Pat Hartman]

if you have to union movements in and out to get a net movement it's a pain as the union queries become non updateable.

I don't think anyone was suggesting separate columns for in and out. The suggestions were to add a transactionType table and add a multiplier so that amounts could be entered as positive.

we seem to have gone back to hard coding In and Out rather than being flexible though.

 

[gemma-the-husky]

@Pat Hartman

I meant if you have a query that evaluates a value as +/- by multiplying a +value by either 1 or -1, then the calculated value becomes non-editable. If you store the value with the correct sign + or - then you don't have that problem. If you realise something is wrong, you can't directly change the calculated value. I am not saying it is always appropriate to edit figures directly either.

I also didn't mean to have have two separate debit and credit columns either - but to sum a column of all positive figures you either need to process all the values by a multiplier, or alternatively deal with the truly positive figures and truly negative figures separately and then union the two. The days of the debit column being nearest the window are long gone.

I also observed that sometimes you may want an adjustment data type with a value that might be either + or -. If you want to store everything as a + then you need 2 transaction types, not 1.

Finally if you view data in a table and see a column of positive numbers it's not always apparent which are positive and which are negative. I can certainly see it either way, but as a qualified accountant (ie - it's more than academic to me), I prefer to store the sign of the transaction value in the transaction. Doing it otherwise adds unnecessary complexity, in my opinion

On a similar topic how would you treat postings to a general ledger. Do you do the same thing? ie have a +/- multiplier for each transaction type to convert figures, or do you store the postings with the signed value, so the general ledger postings can directly be checked to sum to zero.

Hence for inventory, I treat stock on hand as positive, and prefer to store movements out with a negative quantity. I wouldn't process a sale as a negative quantity, obviously, but I would store the sale transaction in the stock movements table with a negative sign.

 

[CJ_London]

hmm. I'm an accountant and advocate single column, multiple transaction types with multiplier - users should not be able to edit values once entered. - and checking general ledger balance is straightforward either way.

However I think it depends on how you construct your system which dictates one method over another so comes down to a matter of preference.

Happy to compare notes some time

 

[Pat Hartman]

multiplying a +value by either 1 or -1, then the calculated value becomes non-editable.

That is correct so you wouldn't necessarily do it in the form's recordsource, it is more for exports and reports. For a form, if you wanted to distinguish +/-, you could use conditional formatting based on the multiplier so that negative fields were red vice versa, whichever makes more sense to the user.

I also observed that sometimes you may want an adjustment data type with a value that might be either + or -. If you want to store everything as a + then you need 2 transaction types, not 1.

I specifically mentioned adjustments. Whenever a type can be + or -, the multiplier is 1 and you enter the natural sign. If it makes more since, then use two transaction types.

I've worked with a number of different systems over the years and custom systems were geared toward what the user department was most comfortable with.

Using the multiplier technique leads to fewer sign errors than just using natural signs. Another way to handle this is to just use the multiplier for verification. If it is positive, then the number entered should be positive and either don't allow a negative or verify that it is an exception.