auto increment numbers based on the value in a different field

[lusdorff]

Need help to figure out how to auto increment one field based on the value in another field. What I have is a table with six fields. There is a foreign key assigned to each record and linked to another table. Four fields in this table are number fields indicating accordingly a number for a category, a number for a subcategory, a number for a subject for a subcategory and a number for an instruction for each subject of subcategory. The last field is the text field for each instruction. Numbers are required to be included in the report. Also, number of categories, subcategories, subjects and instructions vary by each record in the the table which this table is linked to. What I'm trying to accomplish is to have numbers in the subcategory to auto increment starting with "1" for each number in the category field, numbers for each subject field would auto increment starting with "1" for each number in the subcategory field, and numbers in the instructions field to auto increment for each number in the subject field starting, again, with "1". I also need these numbers to restart with "1" for each new record in another table.

Basically, the table would look like this:
cat subcat subj instruction
1------1-------1------1
1------1-------2------1
1------2-------1------1
2------1-------1------1
2------1-------2------1
3------1-------1------1
3------1-------2------1
1------1-------1------1 (if the record in another table is a new/next table)
....and so forth....

I have been looking for anything that could remotely help me. Every VBA code I've tried is either don't work the way I need it to work, other codes making the field to continue increment numbers if the number already exists in the other field.

I hope I'm clear enough to explain my problem.



Any help would be greatly appreciated, I've been trying to solve this for the past three weeks with no luck...
Thanks in advance.

[apr pillai]

Need help to figure out how to auto increment one field based on the value in another field. What I have is a table with six fields. There is a foreign key assigned to each record and linked to another table. Four fields in this table are number fields indicating accordingly a number for a category, a number for a subcategory, a number for a subject for a subcategory and a number for an instruction for each subject of subcategory. The last field is the text field for each instruction. Numbers are required to be included in the report. Also, number of categories, subcategories, subjects and instructions vary by each record in the the table which this table is linked to. What I'm trying to accomplish is to have numbers in the subcategory to auto increment starting with "1" for each number in the category field, numbers for each subject field would auto increment starting with "1" for each number in the subcategory field, and numbers in the instructions field to auto increment for each number in the subject field starting, again, with "1". I also need these numbers to restart with "1" for each new record in another table.

Basically, the table would look like this:
cat subcat subj instruction
1------1-------1------1
1------1-------2------1
1------2-------1------1
2------1-------1------1
2------1-------2------1
3------1-------1------1
3------1-------2------1
1------1-------1------1 (if the record in another table is a new/next table)
....and so forth....

I have been looking for anything that could remotely help me. Every VBA code I've tried is either don't work the way I need it to work, other codes making the field to continue increment numbers if the number already exists in the other field.

I hope I'm clear enough to explain my problem.

Any help would be greatly appreciated, I've been trying to solve this for the past three weeks with no luck...
Thanks in advance.

Check the following Link: Product group sequence with auto numbers