Re Assign/ Reset Numer field?

[loo8866]

Hi there,

Regarding my dance classes database:

The dance classes are split up into categories, and within each category there are a number of dance classes.

I have an 'Order Number' Field. Now, each class has a number assigned to it,which is listed in the 'syllabus'.

Every year some classes are deleted etc, so some numbers will be deleted with the field.

My question: is there a way I can re-organise the number field, so the fields will run incrementely within the category(?).

E.g 1, 2, 3, 4, 5 and 10, 11, 12, 13 instead of 1, 3, 6, 10, 11. and 21, 23, 26, 27.

Thanks.

 

[boblarson]

If you are using an autonumber for this be aware that an autonumber ONLY guarantees you a UNIQUE number and does not guarantee consecutive numbers. If you want consecutive numbers then you will need to create your own way of assigning them. There are hundreds of posts here regarding this. Use search terms such as Custom Autonumber and DMax.

 

[loo8866]

Thanks - Yeh i have been checking them out.



This is the layout each class has in the book:

Category 1: Class 1, Class 2, Class 3, Class 4

Category 2: Class 10, Class 11, Class 12, Class 13
....
Category 9: Class 110, Class 111, Class 112

The idea is that within each category each class number starts from a rounded number (eg, 10, 20, 110) then all classes are sequential in the category.

The trouble is that, each year, some classes are deleted etc, so some categories are not sequential.

What I need to do is to somehow reset the numbering each year -

Possibly I need to write a bit of code which goes through each category and : Looks for next 'rounded number' then sets the numbers for each class again, etc?

Any Pointers?
THanks

 

[The_Doc_Man]

OK, here is the straight skivvy on this. Or at least MY skivvy on this.

I've seen that class-numbering scheme before and I understand that it is commonly used. If you have enough classes that doing it by hand once per year is too much to consider, then here are some issues that you might want to think about BEFORE you undertake the task. You said "dance classes" so I will use dance terms I know.

1. Suppose you have classes in Waltz. So you want Waltz 101, Waltz 102, Waltz 201, Waltz 202, Waltz 203, Waltz 204, etc. So at the end of the year you realize that you need to drop Waltz 202 because you no longer teach that. But the course description for Waltz 203 and Waltz 204 don't change and you have nothing to take the place of Waltz 202.

Resetting the numbers from scratch means that last year's Waltz 203 becomes this year's Waltz 202 (among other changes). So any descriptive materials or curriculum documents are now obsolete EVERY YEAR. If you are good at planning, you ran out of documents just as the school year ended. But if you are like most of us mortals, you guessed wrong and had to toss out some of the supplemental stuff. Do you really want to waste printed material this way?

2. If you want to automate this process, you need some "markers" that will allow your code to identify the correct way to do the re-marking. For instance, if you want to re-number your Waltz classes as noted earlier, you would need a way to know that a class was (1) a Waltz class and (2) a class at a particular level of proficiency. (i.e Waltz 101 vs. Waltz 201 - you need a permanent "proficiency level" marker so these wouldn't become Waltz 101 and Waltz 102.) Otherwise, your automation isn't going to work.

3. You need topic indicators as well as level indicators. Presumably, you would have Waltz 101, FoxTrot 101, ChaCha 101, Meringue 101, Paso Doble 101, Rhumba 101, etc. Note that even if you are using just Dance 100 as Waltz 100 and Dance 200 as Foxtrot 100 in my scheme, the concept is the same, the delineation and names are different.

If your catalog is small enough, doing this renumbering exercise by hand once a year is probably easier and cleaner - and will give you some leeway in keeping or changing numbers from one year to the next. It will keep you in touch with your service (product?) catalog.