Combining tables from two copies of DB

[BruceM]

Our network person did something pretty incredible when changing computers
over to a new server. There were some difficulties in the transition, so he
simply stopped changing over. As a result, for a while people were entering
data in copies of databases on both the new and old server. I will call
this time the Period of Confusion. This had been going on for at least a
week before I discovered that some people weren't seeing changes I had made
because I made the changes to only one copy of the database.
Two databases seem to have been affected. One is a repair record database.
The active table (tblRepairRecord) includes the job number, PO number, etc.
The other table (tblRepairDetails) is for the repair details. For instance,
there may be a record for Repair Manual 123456, Repair 1; and another for
Manual 123456, Repair 7. Once one of these records is added to
tblRepairDetails it doesn't change. tblRepairRecord links to one of these
static records. No records were added to tblRepairDetails during the Period
of Confusion. Records were added to the active table. They have an
autonumber primary key. Now that everybody is once again using the same
copy of the database, how best to combine the tables?
The other database is for training records. Each employee participates in
many training sessions, and each training session may be attended by many
employees, so there is a many-to-many relationship between Sessions and
Employees that is resolved through a junction table. If it matters, there
is no Course table. Training sessions are about the latest revision to a
work method, and things of that sort. Each record in the Training table
contains a record of a unique training session. Records were added to the
Training table in both copies of the database during the Period of
Confusion. With each such record a listing of employees who attended
appears in the junction table. Again, I need to combine the tables. I
mention the database structure in case it has a bearing on how I combine
tables.

 

[[MVP] S.Clark]

Although Queries will help in this situation, there probably isn't a single
query that will do it. You will need to determine what got added, when, and
where, and figure out where to put it.

The hardest part is if there are child records when you have to change the
autonumber value.

btw, appending a row to an autonumber table will assign a new number(just
leave the autonumber field out of the query.)

Good luck,

 

[BruceM]

Thanks for replying. I didn't know quite where to post, so I chose here.
Perhaps I could have made a more appropriate choice. For the simpler
situation I searched around for a while, and eventually came up with a
search string that led me in the right direction. First an append query
(omitting the autonumber field), which left me with a table containing
duplicates of many records (the ones that were created before the network
split). Made a new blank table with the same fields, and combined several
fields into a primary key. Then I appended the table into that new table,
which forced Access to omit records with duplicate PK values. There is only
one child record per record in that table, and since records in that table
never change (new ones are added, but old ones don't change) there was no
problem there.

I agree that the challenge with the other database is the junction table.
The table containing information about the training session is the basis for
the main form. The subform record source is the junction table, which is
linked to the Sessions table through a foreign key field. Employee names
(linked through EmployeeID to the Sessions table) are added to the junction
table by way of the subform.

tblSession
SessionID (autonumber PK)
TrainingDescription
Instructor, etc.

tblEmployee
EmployeeID (PK)
FirstName, etc.

tblEnrollment
EnrollmentID (Autonumber PK)
SessionID (FK)
EmployeeID (FK)
Date

There really are not that many records that were entered into the wrong DB,
maybe 40 or so, so I could just repeat the data entry. One reason I am
looking for another approach is that part of what I am doing is an exercise
to learn how to do this sort of thing.

Another approach would be to append the records into tblSession, then add
the employee names. That would greatly reduce the amount of data entry I
would have to do. I don't see any problem with lost child records if I go
at it from this direction. Am I missing anything?

But what could I do to replace records in tblSessions and their
corresponding records in tblEnrollment? I would need to replace SessionID
in tblEnrollment with the new SessionID for each of the affected records.
That could be tricky. Anything I can think of would involve several steps
at least, and there are still plenty of details I can't quite work out. The
more I think about it the more I think that my second option (tblSession
records only, and add the names manually) is the most practical, but do you
know of a general approach to automating the process? I keep coming back to
thinking I would need to do something like change Autonumber to Number, then
loop through the records and renumber that field to its current value plus
100 (or whatever). I think that cascading update would renumber
corresponding records in tblEnrollment, but I don't know for sure that it
would, nor do I know how to loop through the records to change the number.
Assuming that would work, I could identify the last record in tblEnrollment
that is shared by both databases, then make a table with the same fields but
containing only the records created since the split. Finally I would append
that new truncated table to the other tblEnrollment.
Of course, I would have to come up with a new way to assign a number to
SessionID (DMax + 1 as the default value) as new records are created.

It would be quite an exercise, I'm sure. At this point I am asking more
because of general interest, and to learn new things about Access, than out
of any expectation that such an approach makes sense for relatively few
records.