Counter Query for Maintenance

[theperson]

Hello, I have a problem that seems possible for fix and probably easy for an experienced access user.


I've attached a base example database for easier understanding and to see what I'm working with.


I have a query that rips information from a part# table and orders table to figure out how many 'shots' a part number has ran from a given time. In the example database this time is unlimited. So to start, it will be from order ship dates since 10/1/12, lets say (and the order must have shipped (the check box is ticked)). A report generates what part numbers have over 50,000 shots. This is easy.


Now I want a user, once they have performed maintenance on a certain part, to be able to "clear" that 50,000 shot count record and bring it back to 0 BUT they can't edit the orders table to do that. There has to be a separate table or something, that's where my inexperience is coming in. The shot count starts back up from the date the user clears that record for that certain part.


Hopefully the above makes sense, help would be appreciated, thanks!

[pbaldy]

Can't look at the sample right now, but I think the concept of a "stock take" table in inventory applications could be applied to your situations:

http://allenbrowne.com/AppInventory.html

In other words, You only count transactions after the most recent clearing entry.

[theperson]

I think you have the idea of what I want to do down, but that example seems over my head at the moment and I feel like what I want to do is easier than that.

The main issue I'm running into is how to set records to zero, but not really set them to zero because they are records used for other things in the database. I can query the records from the table fine and put them in a report, but once maintenance is performed on a certain part and user is able to click "maintenanced' or whatever, I'm not sure how to set that part numbers record back to zero without affecting the real record from the table which should not be tampered with.

Any ideas?

[pbaldy]

Well, as you say you don't want to change any existing data. I believe that the concept of how he's handling inventory would work, adapted to your situation of course. You'd have a new table where you entered your "maintenanced" information, including a date (the equivalent of his stock take table). When calculating your shots, you'd only use orders data from the most recent date in that new table for the given part number. In other words, you're not changing any existing data, you're simply controlling what data is included in your calculation with this table. For example, if part 3011 had maintenance done on 10/10/12, when calculating shots you'd only include data from the orders table that occurred after that date.