Addition in table or design issue

[merlin666]

I have a table that has two or three fields that are somewhat dependent on each other.

ItemID which is a number(1,2,3...)
ItemCat which is text(lookup from another table(Mens(100), Womens(200), etc..)
Categorynumber(100,200,300.....)

Is there a way to have it so for each ItemCat the ItemID starts at one and for each item it increments the value for the next record and adds the category number to it. Ie. Item is a mens shirt, ItemID 1 and CategoryNumber 100 so ItemID is 101. Next item entered is 102 but if the Category is Womens it would be 201 etc. Hope that is clear. DB is attached.

 

[neileg]

You'll need to use some VBA so you can't do this at table level. Do a search in here for sequential numbering to get you started.