Changing data on a later date

[thevaik]

I am creating a database where I must be able to remove inventory based on the date an item is to be made.



Right now my inventory table has a "Current" and "Reserve" stock. When my client puts an item to be built at a later date the "Current" stock for that item moves to the "Reserve" stock. My problem is how do I remove that "Reserve" stock on the day the item is to be built?

Thanks!

[Dal Jeanis]

1) How does it move from one status to another currently?
2) How are you tracking whether the item actually *has* been built?

[thevaik]

edit double post

[thevaik]

1) How does it move from one status to another currently?
When user is creating item to be manufactured in database, they select a date. If the date is today it will remove the inventory from Current Stock straight away. If date is in the future it will remove the inventory from Current Stock and add it to Reserve Stock. My client wants as little interaction as possible with the database which has given me quite a few challenges. This removing the reserve stock is probably the most difficult...

2) How are you tracking whether the item actually *has* been built?
The manufactured process is not really tracked. We assume an item is either being built (remove from stock) or going to be built (reserve stock). Once the item is finished being built it gets assigned a license plate which tells us that the item is now in service. This inventory section of the database is just tracking inventory levels so they are not caught off guard without enough inventory for upcoming builds.

[vincent-leeway]

Well if it was MSSQL Server then it could have been possible by writing a trigger, but with Access I do not think it is right approach. I do not know if writing a background process is possible here, which checks the date and automatically removes it from reserve on the day.

[Dal Jeanis]

In essence, you flag the order to show that it is a future date, and reserved but not taken from inventory.

Once per day, you run a query that identifies all flagged orders less than or equal to the current date, and removes the stock from reserve for them, then a second query that unflags them. It's more controlled if you do it in VBA against a recordset of the orders to be handled, but you would be pretty safe with two update queries, as long as they are run within seconds of each other and your ordering process doesn't accidentally reserve something for the same day.

For normalization purists, I would point out that the flag is NOT duplicate data. While any order with a future date would not need such a flag, any order made yesterday for today needs a unique attribute to know that it wasn't made today... And testing whether order date < current date doesn't differentiate between the last time that we ran this, so you could get duplicate updates... not a good thing. I'd use a flag.