How can I make a record number column?

[alexandervj]

I want to make a calculated column that adds 1 for every new record that isnt marked as being 'deleted' and will possibly subtract one if a record is marked as deleted. I'm not sure where to start other than calculated column

[June7]

Can't do that in table with Calculated field.

You have a field that marks a record as 'deleted'? Is this a yes/no field? Why would a new record be marked as 'deleted'?

Why don't you do an aggregate (GROUP BY) Totals query to count records when you need to know this info?

[alexandervj]

Yeah I have a bool that marks a record as deleted and filters it out of normal query results.

Sorry I meant a new record would add 1 to the row/record number and a deleted would take 1 away, the way I wrote it it sounds like i wanted to delete a new record which would be silly.

I dont really care too much about the overall number I just wanted an easier handle to identify to users. Its easier for them to distinguish a part on a datasheet that has a numbered row by it. The autonumber for the ID i dont like using on an end user level because if a record gets "deleted" then there will be gaps

[alexandervj]

plus i never firgured out that problem i was having with canceling out of the new run creation. It still adds a new run id every time you cancel but no run shows up in the runs table as if it had been canceled. I guess its not a big deal, just bugs me. I'm sure users wont be canceling out of new run creations very often anyway

[Xipooo]

Here is a query to produce the results you're looking for and you don't create a calculated table to do it. Base your form or report on this query instead.

SELECT Field1, sum(iif(Field2=True, 0, 1)) AS TotalNotDeleted
FROM TableName
GROUP BY Field1

Assuming Field2 is your yes/no column. Any other columns will also need to be part of your Select and Group By clauses.

[June7]

I don't think you ever provided the db for analysis of the cancel action, which I suggested twice.

Still don't understand need for this sequence number. Users should just be selecting part by some value that has meaning to them - stock number, part number, part description.