Autonumbers conflict between two tables needing appending

[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

 

[hippomedon]

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

 

[Steve Schapel]

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")

 

[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.

 

[Steve Schapel]

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.

 

[hippomedon]

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