Welcome to SPN

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

Sign Up Now!

Autonumbers conflict between two tables needing appending

Discussion in 'Information Technology' started by Ted, Jul 28, 2006.

  1. Ted

    Ted
    Expand Collapse
    Guest

    i use a2k if that counts.

    here's my issue. say there are two identically designed tables from two
    separate mdbs. we need to append the data from mdb 'one' into the
    corresponding table in mdb 'two' and then we'll not use 'one' but stay with
    'two'. as it happens both tables have a field called 'Screening ID' which is
    AutoNumber and the data in 'one' has corresponding values of 'Screening ID'
    belonging to "different* individuals in 'two'. in other john doe's screening
    id in 'one' might be 23 and the value of 'Screening ID' in 'two' might belong
    to mary smith, so we would want to give john doe a value of 'Screening ID'
    once his data were appended into 'two' that was his and his alone.

    what i'm thinking about doing is changing the values of 'Screening ID' in
    each to 'Number' as a first step. as a second step, i'm thinking about
    sorting the values of 'Screening ID' in two from low to high. this will
    determine the highest value. as a third step, i would ask the data entry
    person to recode the numbers of 'Screening ID' in 'one' so as to begin with a
    value one higher than the max we found existed for 'Screening ID' in two. as
    a fourth step i would append the two tables. as a fifth step i would convert
    the property of the 'Screening ID' field in table 'two' back to 'AutoNumber'.
    as new entries appeared in the table 'two', they would once more be assigned
    values incrementally greater than the maximum by a factor of 1.

    can anybody help me out with a reality test. is this going to work? seriously!

    -ted
     
  2. Loading...

    Similar Threads Forum Date
    World Women must play greater role in conflict prevention, UN says Breaking News Oct 28, 2011
    Does the Desire for Prosperity Conflict with Religious Values? Interfaith Dialogues Oct 19, 2011
    Conflict in Us (Self) Spiritual Articles Sep 5, 2011
    Khalsa Aid KHALSA AID Press Release- Libyan Conflict Refugees Relief Sikh Organisations Apr 4, 2011
    Conflicting Maps: Growing Up In An Interfaith Family Interfaith Dialogues Dec 13, 2010

  3. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    You will be unable to convert a number field back into an autonumber
    (don't ask me why).

    Your first 4 steps seem fine.

    Then I recommend the following:
    (1) Add a field called tblName to each table
    (2) fill this in with "tbl1" and "tbl2" for each respective table
    (3) create a 3rd table with the fields from tbl1 and tbl2, but with no
    data. Add a new ScreenID2 field that will be an autonumber. Add a field
    called tblName. Keep ScreenID1 as a Number field. Append tables 1 and 2
    into this new table.

    You will then have a record of the old ScreenID and the original table
    from which they came came, plus a new ScreenID that is an autonumber.
    This way you won't need the Data Entry person to recode anything. . .

    Take care,
    Paul
     
  4. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Ted,

    It all depends on whether these databases contain other tables which are
    related to the tables you are talking about via joins on the Autonumber
    field.

    If so, you will have to cater to this. This is possible, but your
    suggestion does not mention it.

    If not, your idea is way more complicated than it needs to be. All you
    need is to run an Append Query to add the table one records to the table
    two records. Leave everything as it is, except the value that you
    append to the Screening ID field is...
    [Screening ID]+DMax("[ScreeningID]","table two")

    --
    Steve Schapel, Microsoft Access MVP


    Ted wrote:
    > i use a2k if that counts.
    >
    > here's my issue. say there are two identically designed tables from two
    > separate mdbs. we need to append the data from mdb 'one' into the
    > corresponding table in mdb 'two' and then we'll not use 'one' but stay with
    > 'two'. as it happens both tables have a field called 'Screening ID' which is
    > AutoNumber and the data in 'one' has corresponding values of 'Screening ID'
    > belonging to "different* individuals in 'two'. in other john doe's screening
    > id in 'one' might be 23 and the value of 'Screening ID' in 'two' might belong
    > to mary smith, so we would want to give john doe a value of 'Screening ID'
    > once his data were appended into 'two' that was his and his alone.
    >
    > what i'm thinking about doing is changing the values of 'Screening ID' in
    > each to 'Number' as a first step. as a second step, i'm thinking about
    > sorting the values of 'Screening ID' in two from low to high. this will
    > determine the highest value. as a third step, i would ask the data entry
    > person to recode the numbers of 'Screening ID' in 'one' so as to begin with a
    > value one higher than the max we found existed for 'Screening ID' in two. as
    > a fourth step i would append the two tables. as a fifth step i would convert
    > the property of the 'Screening ID' field in table 'two' back to 'AutoNumber'.
    > as new entries appeared in the table 'two', they would once more be assigned
    > values incrementally greater than the maximum by a factor of 1.
    >
    > can anybody help me out with a reality test. is this going to work? seriously!
    >
    > -ted
     
  5. Ted

    Ted
    Expand Collapse
    Guest

    paul, thanks lots for the bandwidth...hope you can help me wrap my mind
    around this....the value of ScreenID in my original 'two' table is an
    AutoNumber field and hence generates thd +1 higher values incrementally for
    the user....will this approach let me convert ScreenID2 from a number field
    to an AutoNumber field called field ScreenID. the name ScreenID is kind of
    sacrosanct as it's used as al linking field in several relationships with
    other tables.

    "hippomedon@googlemail.com" wrote:

    > You will be unable to convert a number field back into an autonumber
    > (don't ask me why).
    >
    > Your first 4 steps seem fine.
    >
    > Then I recommend the following:
    > (1) Add a field called tblName to each table
    > (2) fill this in with "tbl1" and "tbl2" for each respective table
    > (3) create a 3rd table with the fields from tbl1 and tbl2, but with no
    > data. Add a new ScreenID2 field that will be an autonumber. Add a field
    > called tblName. Keep ScreenID1 as a Number field. Append tables 1 and 2
    > into this new table.
    >
    > You will then have a record of the old ScreenID and the original table
    > from which they came came, plus a new ScreenID that is an autonumber.
    > This way you won't need the Data Entry person to recode anything. . .
    >
    > Take care,
    > Paul
    >
    >
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Ted,

    Ah, well if you have related tables, you will need to run an Update
    Query to change the value of the foreign key in the related table to the
    new ID value of the records appended to the main table. It's pretty easy.

    --
    Steve Schapel, Microsoft Access MVP


    Ted wrote:
    > paul, thanks lots for the bandwidth...hope you can help me wrap my mind
    > around this....the value of ScreenID in my original 'two' table is an
    > AutoNumber field and hence generates thd +1 higher values incrementally for
    > the user....will this approach let me convert ScreenID2 from a number field
    > to an AutoNumber field called field ScreenID. the name ScreenID is kind of
    > sacrosanct as it's used as al linking field in several relationships with
    > other tables.
     
  7. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    Ted,

    Steve's idea is must simpler (and better).

    It will keep the autonumber "screenID" in table 1 as an autonumber.
    (Just for clarity I'm assuming you are appending table 2 to table 1.)
    You will need to follow his advice and use that same formula:
    ([Screening ID]+ original Maximum ScreenID from table 1), in an update
    query to update any tables to which table 2 was linked. These tables
    can then be relinked to the newly merged table 1.

    Take care,
    Paul
     

Share This Page