Newbies hawk02 Posted October 4, 2000 Newbies Posted October 4, 2000 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 Posted October 4, 2000 Posted October 4, 2000 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 =-=-=-=-=-=-=-=-=-=-=-=-=
Recommended Posts
This topic is 8800 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now