Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Creating new primary key with relationships

Discussion in 'Information Technology' started by Kyle, Nov 18, 2005.

  1. Kyle

    Kyle
    Expand Collapse
    Guest

    I have been brought into an organization to rework an existing database
    that tracks freezers and other controlled environment units at a
    university. One of the primary issues has been to change the identifier
    for each record from a location-dependent string to a unique numeric
    identifier for each freezer.

    I need to create a new field in the primary table (rather than alter
    the existing ID field), link it to new fields in the secondary tables
    that have relationships dependent on that unique UnitID (without
    deleting or altering the existing identifier field), automatically
    generate new ID numbers in the primary table and have them
    automatically populate out to the corresponding fields in the secondary
    tables.

    Is this possible?

    I tried doing it by creating a new field in the primary table called
    UnitID set to Auto Number, then created like-named fields in other
    tables and establishing a one-to-one or one-to-many relationship
    (depending on the conditions), but the UnitID numbers, of course,
    didn't show up in the new fields in the secondary tables. I tried
    redoing the creation but not setting the UnitID to AutoNumber, created
    the relationships and then tried to change the UnitID field in the
    primary table to Auto Number, but again, of course, you can't change
    the field without breaking the relationship.

    With over 400 records in the database so far, and 500 more to come
    shortly, I'd prefer not to have to do this manually. I'm not adverse to
    using VB to write a script, but have MINIMAL experience with VB. Is
    there a relatively simple solution out there?
     
  2. Loading...

    Similar Threads Forum Date
    Canada Ottawa weighs creating ambassador for religious freedom Breaking News Oct 4, 2011
    India SGPC Accused of Creating a Row Breaking News Jan 20, 2011
    Pacific International Gathering of Young Sikhs Focuses on Creating Good Citizens Breaking News Jan 8, 2011
    Are We Creating New Devtas? Sikh Sikhi Sikhism Aug 21, 2009
    Sikh News Sharif warns against creating instability in Punjab (Express India) Breaking News Sep 19, 2008

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Lots of work. First backup the database before you do anything!!

    Do an update on all the tables to update the new field with the number in
    the autonumber field.
    Delete any relationships using the old primary key and rebuild using the
    autonumber.
    Revise any queries that were joined on the old primary key to be joined on
    the autonumber.


    "Kyle" wrote:

    > I have been brought into an organization to rework an existing database
    > that tracks freezers and other controlled environment units at a
    > university. One of the primary issues has been to change the identifier
    > for each record from a location-dependent string to a unique numeric
    > identifier for each freezer.
    >
    > I need to create a new field in the primary table (rather than alter
    > the existing ID field), link it to new fields in the secondary tables
    > that have relationships dependent on that unique UnitID (without
    > deleting or altering the existing identifier field), automatically
    > generate new ID numbers in the primary table and have them
    > automatically populate out to the corresponding fields in the secondary
    > tables.
    >
    > Is this possible?
    >
    > I tried doing it by creating a new field in the primary table called
    > UnitID set to Auto Number, then created like-named fields in other
    > tables and establishing a one-to-one or one-to-many relationship
    > (depending on the conditions), but the UnitID numbers, of course,
    > didn't show up in the new fields in the secondary tables. I tried
    > redoing the creation but not setting the UnitID to AutoNumber, created
    > the relationships and then tried to change the UnitID field in the
    > primary table to Auto Number, but again, of course, you can't change
    > the field without breaking the relationship.
    >
    > With over 400 records in the database so far, and 500 more to come
    > shortly, I'd prefer not to have to do this manually. I'm not adverse to
    > using VB to write a script, but have MINIMAL experience with VB. Is
    > there a relatively simple solution out there?
    >
    >
     
  4. Kyle

    Kyle
    Expand Collapse
    Guest

    Karl, thanks for the instructions. Like you said, lots of work. I'll
    post again, with details, when I've had success.
     
  5. Kyle

    Kyle
    Expand Collapse
    Guest

    It took me six minutes this side of an eternity to figure out how to do
    what needed to be done, but now that it's done, it seems incredibly
    simple.

    As promised, and for the benefit of anyone needing to do the same and
    finding this discussion through Google, here is step-by-step how I
    copied the unique UnitID numbers from the primary/parent table over to
    the secondary/child table. I would also recommend using Access'
    built-in Help feature to find Microsoft Office On-line Help's 16 page
    instructions titled "Changing existing data by using an update query".

    BEFORE ATTEMPTING THIS - follow Karl's recommendation (above) and BACK
    UP YOUR DATABASE. You will be making permanent changes to one or more
    table fields, and if you have a lot of records, manually going back and
    correcting an incorrect update query will be time-consuming.

    (1) Create a new field in the parent table. Set Data Type to Auto
    Number. On the General tab, set Indexed to "Yes (No Duplicates)".
    (2) Create a new field in the child table (more than one table in my
    case). Set Data Type to Long Integer. On the General tab, set Indexed
    to "Yes (No Duplicates). NOTE: as Auto Number in the parent table can
    only be set to Long Integer, and the Data Types in both fields must be
    identical in order to create a relationship, make sure the Data Type
    settings in the child table field are identical to the Data Type
    settings in the parent table field.
    (3) Close both tables. Open the Relationships window. If the tables
    aren't already displayed, bring them up in the Relationships window.
    Create a One-to-One relationship from the field in the parent table to
    the field in the child table. Close Relationships.
    (4) Create a new simple Query. In the Query, include all data for
    comparison. In my case, I wanted to see FreezerID (the old ID number)
    and UnitID (the new ID number) for every record in both tables, so I
    had four entries in my Query design. Run the Query, and make sure that
    the data being pulled is what you would expect.
    (5) Once you get the data you expect, go to the Query menu, and choose
    "Update Query". The lower half of the design window will change on you.
    Since my UnitID field in the child table was empty and needed to be
    populated with the data from the UnitID field of the parent table, what
    I did was go to the column for the UnitID field in the child table, and
    under Conditions right-clicked to build an expression. In the
    Expression Builder window, I double clicked on Tables, clicked on the
    parent table, and then double-clicked on the UnitID field in the middle
    column, and then clicked OK. Close the query.
    (6) Double-click on the query to run it. Access will give you two
    warnings. The first warns you that you are about to update the table
    data; if you're comfortable with that, click [OK]. The second tells you
    just how many records you are going to update. If the number of records
    matches what you expect, and you feel comfortable, click [OK].
    Otherwise, you still have the option to click [No] and dump out of the
    query. Look at the results, and see if it is what you expected.

    It worked for me, and I hope it works for everyone else!
     

Share This Page