SageForum for UK versions of Sage 50- Help and support.

I am using the Sage 50 ODBC connector and trying to reconcile the QTY_IN_STOCK from the STOCK table for each STOCK_CODE. I am assuming that this should equal the sum of the QUANTITY in the STOCK_TRAN table excluding DI and DO transaction types - however I am getting over 200 discrepancies - would this indicate corruption in the data?

In the Stock Transaction table QUANTITY is the quantity that were received in a delivery, there is another field called QTY_USED.

To reconcile the 2 you need to add up the QUANTITY and take away the QTY_USED this will leave you with the quantity that is still in stock.

This reconciliation process is not needed because Sage does the same thing as part of it's check data routine which should be done every time you backup.

Thank You

but should the QUANTITY of the "Out" transactions not equal the "QTY_USED" of the "In" transactions unless the stock is negative in which case it will match the shortfall?

To calculate the available stock from the stock transactions.

On inwards transactions take the QTY_USED away from the QUANTITY that came in, this will tell you how much of that batch is left add them all up and you know how many you should have.

Imagine this scenario:-

We buy 100,000 of Product A
We sell 65,000 of it in the first year.
The inward transaction will now be QTY 100000 QTY Used 65000 and there will be Outbound records totalling 65000
We then sell 20,000 the next year.
The inward transaction will now be QTY 100000 QTY Used 85000 and there will be Outbound records totalling 65000 in the previous year and 20000 in this year.
At this point we might clear the stock up to the end of the previous year removing the outgoing records of 65000.

So to see what the current level should be ignore all the outbound transactions and add up the inbound QUANTITY and TAKE AWAY the QTY_USED