simple counting query question

[AmandaD]

Hi!

This is probably a really simple solution but I haven't worked with access before at this level and alot of posts here I don't understand.

What I need to do Is:

I have a stock table with an entity NumInStock which contains the amount of of a certain object. I need a query where after I have processed an order I need to subtract 1 from the value held in NumInStock. Thus subtracting 1 from NumInStock until it reaches zero. Is there a way I can execute this on a button on another form. (i.e my sales form)

I have tried to find the answer out through MS online tutorials and help but I must have missed it.

If anyone here can help me with this or point me in the right direction I would be very greatful.

TIA

 

[SunWuKung]

You rarely store calculated values in a database (wisdom taken from Pam). I would do this an other way:

I would have a table that stores the movement of goods
coming in with a positive number
going out with a negative number
You could of course record type of the object the date of the movement, and all sorts of other info - from where to where, why, etc.

But if you sum the amount of in and out goods to any given date that would be your Stock at that date.

Presuming that in your table really there is only one record for any ObjectType (eg. towels) what you ask can be done by:

Update StockTable
Set NumInStock=NumInStock-1
Where ObjectType='WhateverYourTypeIs'

hth
SWK