"Multiple Autonumber" in same table

[Mat Child]

Hi,
I'm trying to find out if there is a way of creating an autonumber like
feature on related records in a table, but there can be multiple instances of
the same number. It's down to Parent Child records - I'll explain

Parent Table (RT) Child Table (Slots)
PK - RT_id (autonumber) PK - SLOT_id (autonumber)
[other fields] SLOT_RT_id (foreign key
link to parent)
SLOT_Number (Long
Integer)
[other fields]

The retionship is one to many with referential integrity cascading updated
and deleted records.
What i would like to do is force the field SLOT_Number to be unique from 1
to whatever, but only within the records relating back to the parent record.
This means there will be duplicated values within this field in the table but
not when you take into account the foreign key.

An ideas would be greatly appreciated.

Thanks

Mat

 

[Stefan Hoffmann]

hi Mat,

What i would like to do is force the field SLOT_Number to be unique from 1
to whatever, but only within the records relating back to the parent record.
This means there will be duplicated values within this field in the table but
not when you take into account the foreign key.

You can do this by using

Nz(DMax("SLOT_Number", "Slots", "SLOT_RT_id = " & [idParent]), 0) + 1

in either a query or in a Form Before Insert event.

btw, you need at least an unique index on (SLOT_RT_id, SLOT_Number). You
may consider using these two fields as primary key depending on your
further usage of the Slots table.


mfG
--> stefan <--

 

[Mat Child]

Brill,

It worked a treat, cheers for that.

hi Mat,

What i would like to do is force the field SLOT_Number to be unique from 1
to whatever, but only within the records relating back to the parent record.
This means there will be duplicated values within this field in the table but
not when you take into account the foreign key.

You can do this by using

Nz(DMax("SLOT_Number", "Slots", "SLOT_RT_id = " & [idParent]), 0) + 1

in either a query or in a Form Before Insert event.

btw, you need at least an unique index on (SLOT_RT_id, SLOT_Number). You
may consider using these two fields as primary key depending on your
further usage of the Slots table.


mfG
--> stefan <--