Number data type showing negative (-) numbers

[Hek]

Hey all

On my access DB i have noticed that the "on order" values for one of my number data types is providing negative values, even the number itself isnt correct if i were to simply take the - sign away.

Attached is a screenshot of where the issue is presenting itself.

If anyone has any ideas, it would be greatly appreciated.

Regards, Rhys.

EDIT: Very new to Access, keep that in mind

 

[June7]

Interesting that all the non-negatives are 0. This is a calculated value? What is the calculation? Post query statement. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Hek]

Hey there

it is a number data type, not calculated.
Not sure why this is happening, any thoughts?

 

[June7]

No idea. Would have to review db.

 

[Hek]

how would i make a backup DB and wipe all the confidential info off to post here?

 

[June7]

Just copy the db as you would any file. Then open tables and delete records. Or run UPDATE queries to selectively empty fields.

 

[Pat Hartman]

You cropped the first picture too tightly. Are you showing a Form or a table in DS view?

OnOrder might not be calculated in a query but it is surely calculated based on an order as it is placed. That is one of the no-no's of normal database design. Calculated values should not be stored. You can obtain the OnOrder quantity by summing all the non-received orders.

 

[arnelgp]

You can obtain the OnOrder quantity by summing all the non-received orders.

not always the case.
https://bizfluent.com/how-6551577-calculate-reorder-quantity.html
https://www.inflowinventory.com/blog/reorder-point-formula-safety-stock/
http://www.accountingexplanation.com/reorder_level.htm

 

[Cronk]

In the original post, was that a screen shot of the table? It might be that Stock on Order is treated as negative usage, depending on system design. How do the numbers appear on form(s) relating to ordered stock? Creating a query based on the table with a negative in front of the field would display positive numbers.

 

[Pat Hartman]

not always the case.

The quantity on order can be obtained by summing the open orders (i.e. Orders that have not yet been received)
The links you posted were for how to calculate how much to order and when to place the order.

 

[arnelgp]

it's obvious, the program has a calculation of its own.
accounting standard I supposed.

 

[Pat Hartman]

I did mention back in # 7 that on order was calculated in some manner. But on order is not the same as figuring out how much to order and when. On order simply reports what is already in the pipeline. It may be part of calculating how much to order but they are not the same thing.

 

[Hek]

Private Sub Command34_Click()
If [Quantity] <= [Stock] Then
Me.Stock = Me.Stock - Me.Quantity
Me.Picking_ID = 4
Me.Fabrication_ID = 4
Me.Finishing_ID = 4
Me.Pressure_Testing_ID = 4
Me.Packing_ID = 0
Me.On_Order = Me.On_Order + Me.Quantity
Me.Status_ID = 0
Me.InventoryID = 2
'Inventory is good on this part'

ElseIf [Category] = "Catch Can" Then
Me.Picking_ID = 4
Me.Fabrication_ID = 4
Me.Finishing_ID = 4
Me.Pressure_Testing_ID = 4
Me.Packing_ID = 0
Me.On_Order = Me.On_Order + Me.Quantity
Me.Status_ID = 0
Me.InventoryID = 2

ElseIf [Quantity] > [Stock] Then

Me.TempNumber = Me.TempNumber + Me.Stock
Me.StockTemp = Me.StockTemp + Me.TempNumber
Me.TempNumber = Me.TempNumber - Me.Quantity
Me.TempNumber = Me.TempNumber * (-1)
Me.Quantity = 0
Me.Quantity = Me.Quantity + Me.TempNumber
Me.TempNumber = Me.TempNumber + Me.On_Order
Me.On_Order = Me.TempNumber
Me.Stock = 0
Me.Picking_ID = 1
Me.Status_ID = 0
Me.InventoryID = 2

End If
Me.TempNumber = 0
DoCmd.GoToRecord , , acNewRec
DoCmd.OpenForm "Master Form"
End Sub

Here is the code that i think is responsible for the issue, anyone have any thoughts?

 

[June7]

Saving aggregate data should be rare and only with strong justification. Review http://allenbrowne.com/AppInventory.html

 

[arnelgp]

the calculation will result in positive On Order.
maybe somehow somebody mess with the db and directly manipulate/enter data on the table directly and not by form.
you should rectify the table by going through all transactions and manually compute the totals.