Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unique Key (AC97;AC2K)

    I am trying to create an auto number key for a detail record, which will reset to 1 when a new Master Record is created.

    Example. (Time Recording)
    Master Record contains (among others)
    TSheetId; Autonumber; key
    Date;
    Employee Id;

    Detail Record contains
    TSheetId;
    Hours Worked;
    JobNumber;
    CostCentre;

    NOTE: Validation is such that either a Job Number OR a Cost Centre Number are entered. (Table Level Validation)

    There will be many Detail Records to one master record. Whilst the combination of Job Number and Cost Centre will be unique, one or the other will be Null. I am looking for an approach will give me a key for the Detail Record. I was looking at trying somehow to set the key of the detail record to 1, incrementing for each new Detail record, so that the unique key for the detail record would be TSheetId;DetailId. Setting the detailRecord key to autonumber would work, but I want to display line item numbers on the subform for the detail record.

    TIA to all who respond

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Unique Key (AC97;AC2K)

    From an updatability perspective, you probably want to add an autonumber that is the primary key for the table, just to keep Access happy. However you will want to index the various fields so you can get reasonable performance in looking up detail records. One way to set an index number for each detail record would be to do a dmax query to find the largest number currently used, and when you add a detail record, you would assign the next number + 1 to the new detail record. A better approach from a multi-user approach would be to store the next index number for detail records in the Master Record. Thus adding a new Master Record would set that value to 1. However, you might look at a different design approach:

    Does the Master Record table store both Job Numbers and Cost Center Numbers? If not, I would consider doing that. If that's already the case, I would change your detail design to use a pointer to a record in the Master Record table, so that there would never be a null in the pointer in the Detail Record. Then you could use that in combination with a detail record counter to always have a unique key, thus enforcing the uniqueness without the use of an autonumber field. If this raises other issues, feel free to post back.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Key (AC97;AC2K)

    Thanks Wendell,
    You have given me the clue with the idea to store the next index number for detail records in the Master Record. I guess I was looking for a nudge in the right direction. You have provided that. Database is still in planning phase, so I can now include this in design

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •