Help please: I need an autonumber field that can reset based on another yes/no field

[munky]

Access 2007, beginner level.



Imagine a db to track automobile usage/service/efficiency etc. Each record in my main table is one start of the car, with an autonumber as the key field/ID. I don't want to change that.

I have an "oil change" field with a yes/no checkbox, for trips when I got the oil changed. Less than 1% of my records have this selected as "yes".

I am trying to create another field that will tell me how many starts since the last oil change. I would like it to automatically populate "1" anytime the preceding record has "oil change" selected, and auto-increment +1 for any subsequent record. So for example I would be able to see that this particular record is the 237th start since the last oil change.

If it can't restart based on the oil change field, I would like to be able to manually enter "1" in post-oil change rows and have it update subsequent records.

I would like this to work for my existing records once I implement it (without having to repopulate all the other data.

PS I am not a coder so munky-friendly solutions are greatly appreciated. Right now I'm looking at manually populating each cell myself...1, 2, 3, 5, oops backspace 4, 5. Is there a way to save time but maintain accuracy?

Thanks!

Munky

[ItsMe]

Store each event in a table. You are trying to edit existing records where you should be appending records.

Oil change? Create a new record in your tblMaintenance

Data download from vehicle? Create a new record in tblDiagnostics

Vehicle assignment? Create a new record in tblAssignments

If you are keeping track of vehicle assignments in a paper ledger, someone checked in a vehicle, then you checked that same vehicle back out again, you would not go to the ledger and erase the name and date to replace it with a new name and date.

[munky]

Sorry if I'm being dense but I don't think you answered my question. for any given record, I want to be able to know how many trips it's been since the oil change. I understand that you want me to create a bunch of additional tables but how will that accomplish what I'm trying to do? thanks

[ItsMe]

You may not have to create many tables but you will want a normalized structure and I do not see how you are going to get the data calcs desired without having a specific table for a specific event.

Also, post #1 describes editing the value in a specific record to represent a vehicle's current state. Instead, add a record.

So, normalize your data structure and add a record each time an event occurs.