Copying records from the "many" end of a relationship to a new ID on the "one" end

[WOW News]

Hello,
I need to copy a set of related records from the "Many" end of a "One to
Many" relationship. I don't know how to put this in words other than with
an example so here goes:

There is a table for Salespersons that is the "one" of a One to Many
relationship with several other tables (contacts, products, sales).
The primary key in the Salespersons table is SalespersonID which is linked
to many related records in each of the other tables.

If SalespersonID01 decides to leave the company or takes a different
position and we wish to add a new SalespersonID02 who will take over all the
same contacts, products, and sales, is there a way to copy all the related
records from SalespersonID01 to SalespersonID02?

I don't want to delete or reassign SalespersonID01 because I need the
historical records for that person. Basically I am trying to avoid having
to manually re-enter all the contacts, products, and sales for
SalespersonID02 by allowing the user to copy all the related data for
Salesperson01.

Thanks for any guidance you can provide!

 

[Allen Browne]

You can do that with an Append query.

1. Create a query using the table you want to duplicate records from.

2. Type in some criteria (e.g. SalepersonID01)

3. In a fresh column in the Field row, type in a number representing the
person you want to append to, e.g. SalespersonID02.

4. Change it to an Append query (Append on Query menu.)
Answer that you want to append to the same table.

5. Map the correct fields. You do NOT want to append to any field for the
one in step 2, but you DO want to append to the SalespersonID for the field
in step 3.

6. Once the query is correct, switch to SQL View (View menu.)
There is a sample of the query string you need to create in VBA code, to
execute.

If executing append queries in code is new, see the Execute part of this
article:
http://allenbrowne.com/ser-60.html
The basic idea is to concatenate the values into the string, so it adds the
correct information.

Here's another example:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
This one shows how to duplicate the sales person and their related records.

 

[WOW News]

That was just what I needed to get me "unstuck" - thanks very much!