Inventory Control, Stock = Stock - 1

[bricklebrit]

On a subform [orderDetails], I'm attempting to create a system to keep track of stock of items.

My values are:
[seriesTitle] is product name from the [OrderDetails] field
[unitsInStock] is the number of current items in stock stored in the [products] field

In a form, after I enter the value for [seriesTitle] for the product entry, I would like the value [unitsInStock] from the field [Products] to subtract one from itself (unitsInStock=UnitsInStock - 1) and display this value in the same subform.

Is there a simple expression that will achieve this?

Thanks in advance for any help!
Jon

 

[dynamictiger]

Stock is a calculated value. as such it should not be stored in any table. Rather calculated on a required basis.

To acheive this you should have ordered amount, sold amount, and maybe allocated amount.

Therefore

Ordered amount-soldamount-allocatedamount=stock

This will in all likelihood fix your problem but requires a rebuild.

HTH

 

[bricklebrit]

Thanks HTH!

I kind of knew that, but I'm glad you explained it out -- I've been able to successfully rework the database to reflect amount - amount sold = stock

Thanks again!

 

[james33]

Dear Tiger,
Allocated Amount
What's that, could you please explain?
Regards Jim

 

[dynamictiger]

Allocated amounts are amounts of stock that are on order by a client for say a very large job, but not picked yet.

 

[james33]

Thanks!

 

[bricklebrit]

Thanks for all the information! The database is working great.

Is there a simple equation to calculate the requiremented stock for individual items for two weeks based off past orders?

 

[dynamictiger]

You could average the stock over the last two weeks.

I wouldn't do that though. My experience with stock is that no one knows from one week to the next what might happen.

I think that it may be possible to do a kinda best guess. The maths escapes me right now, but it would go something like if average stock is this, and this weeks movements has been that, last years stocks were y and we ran out of this stock last year then we guess z quantity.