inventory amount

[jarheadjim]

My database tracks an inventory of Haz-Mat, so even when someone uses all of a material they have to check the container back in. So what I'm trying to figure out is how to make the database do something like this

[qty on hand]= [qty on hand]-[[qty checked out]-[qty checked in]]

does that make sense? i'm not sure if i can tell it to subtract an amount from itself and then save the new total.

 

[jarheadjim]

ok, i'm getting there:

Expr1: [quantity checked out]-[quantity returned]

Expr2: [qty on hand]-[expr1]

now how do i make [qty on hand] save expr2 as it's value?

 

[Idjit]

Try using an Update query. Add the field [qty on hand] to the design, and under "Update To" type [qty on hand]-[[qty checked out]-[qty checked in]]

 

[jarheadjim]

no joy, it gives me the "invalid syntax" error message. can i just make [qty on hand]=[expr2]? if so where?

 

[Idjit]

It could be something simple - use [] only to enclose variable names, not as parentheses. Actually you don't even need parentheses, under update to: type [qty on hand]-[qty checked out]+[qty returned]

Let me know if it works.

 

[jarheadjim]

this is the route i'm trying now:

when an item is checked out the [qty checked out] has an after update query macro that subtracts it. then the [qty checked in] has an update query macro that adds back in all the items not expended. the first part works great, but for some reason the second macro deletes the entry entirely

am i making this harder than it really is?

 

[Idjit]

Do you want to run the update query periodically, like at the end of each day, or do you want total on hand to immediately update every time someone checks out or checks in a product? If you want immediate updates, you may not need a query at all - I'd be tempted to perform the calculations within a form.

 

[jarheadjim]

i got it, thanks for the help