If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Multiple Autonumber" in same table
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 |
#2
|
|||
|
|||
"Multiple Autonumber" in same table
hi Mat,
Mat Child wrote: 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 -- |
#3
|
|||
|
|||
"Multiple Autonumber" in same table
Brill,
It worked a treat, cheers for that. "Stefan Hoffmann" wrote: hi Mat, Mat Child wrote: 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 -- |
Thread Tools | |
Display Modes | |
|
|