"Multiple Autonumber" in same table

  • Thread starter Thread starter Mat Child
  • Start date Start date
M

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
 
hi Mat,

Mat said:
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.
Click to expand...
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 <--
 
Brill,

It worked a treat, cheers for that.


Stefan Hoffmann said:
hi Mat,

Mat said:
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.
Click to expand...
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 <--
Click to expand...
 
Back
Top