Subdata Sheet AutoNumber

[Nushan]

Hi ! New to Access here. I have 2 questions, it would be much appreciated if someone can help me.

1. How to make AutoNumber in Sub Categories table based on Category Auto Number

Eg: Table [Categories] : TST-1000 Telephone & Internet
Table [Sub Categories] : TST-1001 TP-0001 Group
Table [Sub Categories] : TST-1002 TP-0002 Group

Eg: Table [Categories] : TST-3000 Selling & Distribution
Table [Sub Categories] : TST-3001 Marketing
Table [Sub Categories] : TST-3002 Packaging

2. How to make Specific AutoNumber (Not related to attachment)

Eg: Income : INC-1000


Expense : EXP-1000

[CJ_London]

just have 2 fields, an autonumber and category, you can concatenate the two when required

specificNum:[category] & "-" & [autonumber]

if you want the same number part for each category. google 'dmax+1', but this will come with issues in a multi user environment.


Note in your table design, Description is a reserved word and should not be used as a field name. similarly field names should not have spaces or use none alpha numeric characters. If you do, they can cause problems.

Also advise you for PK/FK use autonumber/long field types. Up to you, but using text will create larger indexes which will affect performance once your tables get to any sort of size.

[Nushan]

Thank you Ajax,