microsoft access 2016 - autonumber value with text prefix- relationship problems

I'm trying to achieve a autonumber with a text prefx I can build relationships between tables with eg INV00001 for invoice 00001 etc

at first I entered "prefix"000 in the format box of the autonumber field however you cant build relationships as autonumber is numeric cant have text.....

so then I tried creating a calculated field to take the autonumber number and apply a prefix that way, "prefix"&[automnumberfield] which also created the desired result, but once again you cannot build relationships between calculated fields......

is there any other way to achieve a autonumber with a text prefix you can build relationships with?


Yes it is possible (see my DMax+1 comment above) but it is not recommended, especially not if you are new to database design and should really follow all best practices.

You can have

CustomerID autonumber PK

CustomerNumber text(10) required uniqueindex

CustomerName 'etc.

Then populate CustomerNumber using DMax+1 technique.

In the Orders table, CustomerID is the foreign key.

Microsoft Access MVP
Phoenix, AZ

1 person was helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated September 16, 2020 Views 1,393 Applies to: