Auto-enter that gets the highest number+1

February 20, 201016 yr

Is there a calculation that would allow me to get the highest value in a field and then ad 1.

I can't think of a function that I could use to get the highest value in the dataset.

Thanks

Kevin

February 20, 201016 yr

Max ( ) with a self join? Hopefully, you're not trying to generate a serial ID for use as a key with this function.

February 20, 201016 yr

get the highest value in the dataset.

It depends on how you define "the dataset" - it could mean the found set, or all records in the table.

Either way, you can only get the highest value in the dataset AT THAT MOMENT - so if two users create a new record each at approximately the same time, you can very well get a duplicate value.

February 20, 201016 yr

Author

Max ( ) with a self join? Hopefully, you're not trying to generate a serial ID for use as a key with this function.

It is going to act as an ID, but not the only one. There is an absolute ID that is a standard serial number, but what I need this to do is be a bid number but the users sometimes start a new record and then delete it so the bid numbers have holes and they are really tired of those holes. I am trying to recreate what other Databases do with serial numbers by only incrementing on the highest number.

February 20, 201016 yr

Author

It depends on how you define "the dataset" - it could mean the found set, or all records in the table.

Either way, you can only get the highest value in the dataset AT THAT MOMENT - so if two users create a new record each at approximately the same time, you can very well get a duplicate value.

I wouldn't want it to be influenced by the found set, but that just might not be possible. There is really only one user at a time so duplicate values at this stage are not a problem but it is good to always keep that in mind.