Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    11

    Unanswered: appending into one-to-many table structure using auto-increment Primary Keys

    Hi,I hope someone can help.

    I've got a project coming up where I'll be working with scientific data that has been collected
    over many years by many people. The dbase and app. was originally created in Access, rolled up to Access 97,
    2000 and then 2002. There is a master copy and an number of 'distributed' copies where each will have new data
    that needs to be added to the master. Any one of them could be in Access 97, 2000 or 2002, each with different
    versions of the data-model and application.

    Additionally, the schema uses a series of complex primary keys. At the most detailed level, there are up to 9 fields creating
    the primary key (PKY) for a single table. The system works, sort of but not without its challenges.

    My question is this. To make it easier to work with the data, I want to redesign the data model and encorporate the use of
    auto-increment fields for the primary keys. When I populate the new model with data, I'm not sure of an efficient
    method of cascading the primary/foreign key relationships down to the one-to-many relationships.

    I plan to automate the proecedure w/VBA as I can manipulate/correct the data while populating. What I'm not sure about is getting
    a new record's primary key value passed from the parent table down to the child table in the foreign key field. I've got some
    ideas and have done this before but I believe my method, is clumsy.

    eg:

    lets say the parent table has 112 records and the current PKY value = 112

    A newly inserted record into tblParent.PKY_AutoIncrementValue = 113 and is the FKY value to join to
    the child table on the FKY. The new tblChild.PKY_AutoIncrementValue = 2027 and is then used to join tblChild_Child.FKY_Value
    And so on and so on...

    The way I've tried this before was to insert the new record into the parent table;
    Then, query for the max PKY value (113) and pass it using a variable to be inserted into the child table as the FKY value. Then query for the new Child Max PKY value in the child table (2027)
    to be inserted into the Child_Child table.


    It worked but there's a lot of code involved for only a few tables. This dbase has over 40 tables and there's got to be a better way to do it. I haven't found any examples on this.

    I'm more of a data modeler than developer and would very much like to hear your thoughts on this.

    Many thanks.
    Last edited by dbarends; 11-26-07 at 10:55.

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    There is a lot of convolution there. I think your asking for advice on how to bring all the disparate data together as well as possibly release a new version of the app.
    In which case, I would lose all the matching of auto increment numbers and that sort of thing. It is imperative that you figure out what the real uniquely identifiable info is and use that to correlate the data.

  3. #3
    Join Date
    Nov 2007
    Posts
    11
    Hi and thank you for your reply,

    There's a number of issues with this dbase that just makes it difficult to work
    with unique values other than an autoNumber pimary key structure:

    The database hasn't been normalized properly. I need to take it to the third level.
    Currently, some of the detailed tables (child_child_child.....and so on) have over 100,000 records. Once normalized, some tables will have over 200,000 records.
    The current primary key constraints have been 'unset' and they are not all unique due to null values etc. Part of my mandate is to also 'clean' the data.
    Currently, queries aren't working properly due to all of this and other issues.

    Using the autoNumber will be a huge help when adding data from the satellite data sets to be put into the new 'master'.

    Knowing the approach or method to program or configure the act of carrying an updated parent autoNumber primary key down to the related child PKY for bulk data would be a tremendous help.

    I'd appreciate any help you could offer.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at guid's and or replication

    failing that you are going to have to introduce a new element to your PK eg dbID

  5. #5
    Join Date
    Nov 2007
    Posts
    11
    I'm guessing that I'm going to have to make my update procedure a multi-step approach. Fortunately, the project is not in my lap yet but do expect it anytime soon.

    I know what GUIDs are (sort of) but haven't worked with them yet. I'll check into it and see if I can find some reference material on it.

    Thanks for the suggestion.

Posting Permissions

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