Resetting Query but not Table Question

[baseball2012]

Hello, I have a possibly hard or easy problem that needs a solution. I've attached an example database to help explain my situation.

In the "Order Entry" table, a certain part # will be added with a quantity and a due date. Once the part # is shipped, the checkbox will fill in.

What I want to do, is create a way to accumulate the quantity ordered for each part# once it has shipped, and have a user be able to open a report saying which part #'s have over 50,000 shots. To determine shots, the order quantity will be divided by the cavities in the "List" table. I imagine that will be done in a query.

I created the "tbleShotCount" as a way I was thinking to store the shot count information. Once it gets above the 'CleaningDue', the 'NeedsClean' box will be automatically checked off and the part# will show up in a report.

Once the user has cleaned the part#, the user will click something and the shot count in the "tblShotCount" will be reset to 0 and the process will be repeated. The "Order Entry" table can not be modified or the information changed throughout this whole process.

My biggest problem was figuring out a way to reset the shot count without effecting the "Order Entry" table.

Help would be appreciated, thank you!



ExampleDatabase.zip

[orange]

You should realize that we only know what you tell us. I have no idea what you are trying to do. Shot count, cleaning... it's all jargon.Can you step back and tell us n 4 or 5 sentences WHAT you are trying to do in plain English.
What is your environment, what business are you in.....

[baseball2012]

I'm sorry for the confusion. I've made some progress towards my goal, so I think I can explain it in more simpler terms now.

I've reattached my modified database for reference.

If you open "frmCleaningSheetTemp" and choose a part# from the drop down list and check off the 'level 2 pm' box, then in "tblShotCount", whatever part# you chose 'order quantity' will go to 0. (After you update it using 'qryShotCount").

When a new order is entered into "Order Entry", I want the 'order quantity' field in tblShotCount to continue adding order quantity amounts after the date of which that check box was checked originally.

Basically, without being specific, once a field gets zeroed out, I want that field to start adding numbers again after the point of zeroing (use date and time?) automatically.

Right now it just stays at 0 regardless if I enter in a new order quantity in "order entry" or not.