serial numbers

[hawk02]

I have a database that has sequence numbers, in the field "Sequence", that increment by 1 everytime a new record is created. The number needs to start at 1 each month and go as high as it necessary until the end of the month. When the next month begins, that field needs to be reset to 1 again. IS THERE A WAY TO DO THIS AUTOMATICALLY? Otherwise, I will have to remember to go in and reset the numbers manually, which is an option I do not care for. Please help!

[Kurt Knippel]

quote:

---

Originally posted by hawk02:

I have a database that has sequence numbers, in the field "Sequence", that increment by 1 everytime a new record is created. The number needs to start at 1 each month and go as high as it necessary until the end of the month. When the next month begins, that field needs to be reset to 1 again. IS THERE A WAY TO DO THIS AUTOMATICALLY? Otherwise, I will have to remember to go in and reset the numbers manually, which is an option I do not care for. Please help!

---

Automatically? Hmm, that could be difficult. You could use a self-relationship by some kind of compound key.

Create a calculation called MonthKey = Date(Month ( CreationDate ), 1, Year ( CreationDate ) ). This assumes that you have a field called CreationDate which is simply an autoentered date created field.

Now create a self-relationship (Self by MonthKey), sorted in descending order by SequenceNumber.

Finally make your SequenceNumber a number field with an auto-entered calculation something like this : If ( IsValid ( Self by MonthKey::SequenceNumber ), Self by MonthKey::SequenceNumber + 1, 1 ).

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=