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!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"WOW News" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!

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

"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "WOW News" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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!
>