Database Design question

[merlin666]

I am working on an auction database and have a basic framework(see attached). However I can't seem to get the categories to work right. It was requested that each auction item be able to be assigned to a category and then the item be assigned a number based on that. Ie mens would be in the 200's and womens would be in the 100's. It 100 and 200, 101, 201 etc. I did some searching on sequential numbering but nothing seems to work. Do I need seperate tables for each category?

thanks in advance for any help.

 

[HiTechCoach]

... Do I need seperate tables for each category?

No, I would not use a separate table.

How to Create an Incrementing Record Number

 

[merlin666]

I've figured out how to use incrementing numbers but not how to have a different set of numbers for each category.

ItemID|Category|CatNum|ItemDesc|
1 |Mens |100 |Watch|
2 |Mens |101 |Laptop|
3 |Womens |200 |Dress|
4 |Childrems |300 |Train|
5 |Womens |201 |Massage|
.
.
.
etc
Does that help to clarify. My form has a Combo Box for Category and the CatNum would autoincrement based on the category field.

 

[gemma-the-husky]

two ideas

1) keep a separate table with the next number to use for each given sequence.
this allows you to reset sequences if you need to

or

2) use a dmax, to find the highest number currently on file. Then add 1 to it, to get the next sequential number.

syntax something like

highestnumber = dmax("fieldname","tablename","section = " & whatever)


the red bit is necessary, so that a dmax for men, finds the highest value for the men section only, etc

I am sure this is the type of thing Boyd has shown you above - this corresponds to your catnum in the combo box, which you mentioned