Append query with recode

[Kurt]

Hopefully a simple one here: In the following example, I
need to append the data in tblTeachers_New to
tblTeachers.

tblTeachers
-------------------------------------------------
AutoNumID TeachID(PK) SchoolID TeacherLast
1 02025 3 Jones
-------------------------------------------------

tblTeachers_New
-------------------------------------------------
AutoNumID(PK) TeachID SchoolID TeacherLast
1 03018 4 Smith
-------------------------------------------------

There are two immediate problems:

1. The PK fields in each table is different.
2. The value of the AutoNumID field is the same in each
record, and AutoNumID in tblTeachers will not (and should
not) allow duplicates. (Imagine that each table has
several hundred records in them, so it's not practical to
manually change the AutoNumID values to avoid
duplicates.)

What's the best way to recode the data so tblTeachers_New
conforms with tblTeachers, thus allowing the append?

Thanks. Kurt

 

[[MVP] S. Clark]

When appending from T_New to T, don't worry about the AutoNumber in T_New.
Just append the rest of the fields, and let Access create a new AutoNumber
for each record. Afterall, don't you really only care about the TeachID not
being duplicated?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Kurt]

When appending from T_New to T, don't worry about the

AutoNumber in T_New. Just append the rest of the
fields, and let Access create a new AutoNumber for
each record.
HTH

Thanks. For some reason I was thinking that Access would
not automatically create AutoNumbers and that that the
query would fail. - Kurt