Hi,

I have a table that looks like this:
main-table:
id as auto-numbering
unid as string
catid as number
zoneid as number
name as string

cat-table:
id as auto-numbering
catname as string
catshort as string

zone-table:
id as auto-numbering
zonename as string
zonenumber as number

This is the query I want to use:
SELECT zone.zonenumber & cat.catshort & max(CInt(main.unid)) AS mainNewProdId
FROM tbl_categorie AS cat, tbl_main AS main, tbl_zone AS zone
WHERE (((cat.id)=[main].[catid] AND ((zone.id)=[main].[zoneid])) AND ((cat.id)=[Forms]![frm_nn]![catid]));

I need to create a number consisting of the zonenumber, category and main unique id.

Why do I use a main unique id:
In the table I use printers, computers, etc ...
each device has its own number (NOT autonumbering) because for each device it starts with 0000001

The thing is that I want a unique identifier that looks like:
5535INF0000001 for printer1 and when I want to add a new one I choose category printer and I get the next number: 5535INF0000002

Does anyone know a good sollution for this problem?

Thanks in advance!

Separate the PrefixCode and Sequence number into separate fields.

Increment the sequence number selecting only the records with the appropriate prefix:
DMax("SeqNum", "tablename", "tablename.PrefixCode = Forms!formname.somecontrol") +1

This is sufficient for single users adding new records. However if there are multiple users adding records you need to use more sophisticated techniques.

Reassemble the fields to the full identifer on the fly as required:
PrefixCode & Format(SeqNum, "0000000")