Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    updating autonumber based keys (2000/XP)

    I have a db whose main tables are related as follows (also see attachment for a schematic db):

    Parent table (tblSites) with autonumber field as index (no duplicates). Primary key (SiteRef) is derived by concatenating a series of component fields.

    Child table (tblExams) where SiteRef is a foreign key. A combination of SiteRef and ExamDate actually make the record unique but rather than use a composite key (something I have never used/have no experience of) the primary is an autonumber field (ExamID).

    ExamID links through as the foreign key in the 'grandchild' table (tblReports).

    The problem I have is that I need to combine 5 years of examinations ( a few thousand records in each) where each year was entered into a separate database with the autonumbers beginning at one.

    I'm a little unsure how to maintain the relationship between the exams and reports. Any suggestions gratefully received.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating autonumber based keys (2000/XP)

    One thing that might work is in a new database, link to the tables in question. Create a make table query for each of the tables, qmSites, qmExams, qmReports. In the field that is the Autonumber, rather than just copying it from one table to the next, for the first year do something like 10000 + [AutoNumberField]. This will maintain the ID, and allow you to add additional year with the same AutoNumber. Do the same thing for the keys in the other tables that related back to the parent table. For each additional year add 10000 to the key.


    Maybe not the most elegant solution, but it should work.

    Hope this helps
    Richard

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating autonumber based keys (2000/XP)

    Thanks Richard, I think that should do the trick. If you (or anyone else for that matter) are feeling inspired, I have created a new <post#=559,641>post 559,641</post#> that is related to this issue. I'm looking for a alternative to using an autonumber for the primary key.

Posting Permissions

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