I have a database with the following structure:

Table 1
++--Table 2
++++--Table 3
++++++--Table 4
++++++--Table 5

I need to create a copy of a record in Table 2 and all
related records from the tables beneath table 2 and
assign the data to another record in Table 1, while still
maintaining the original data and assignments.

Table 2 is the many side of table 1.
Table 3 is the many side of table 2.
Table 4 & table 5 are both the many side of table 3.

Any help would be appreciated.
Thanks!

"John" <(E-Mail Removed)> wrote in
news:0e3201c37c5b$efc18090$(E-Mail Removed):

> I need to create a copy of a record in Table 2 and all
> related records from the tables beneath table 2 and
> assign the data to another record in Table 1, while still
> maintaining the original data and assignments.
>

No you don't. I suggest you read up on database design and something on
relational theory and "normalisation". There just isn't a valid reason for
copying fields from one table to another.

Best wishes


Tim F

Perhaps there is a different way to do this but...
I need to duplicate a record to assign it to multiple
users.

Here are the specifics:
An instructor (table 1)
Creates an educational map (table 2)
That contains units (table 3)
That contains content and standards (table 4 & 5)

A group of instructors got together this summer and
created a fake "instructor" so they could work together
on these lesson designs. Now each instructor wants the
design information in "their" profile (table 1) so they
can make their own individual changes and customize the
template that they created in a workshop.

It may not be good normalization practice but this is
what I need. I can do it record by record but that is
tedious. I simply want to copy a record and all related
records to another area.

Hope this paints a better picture of what I need.
Thanks for your advice.
-John

>-----Original Message-----
>"John" <(E-Mail Removed)> wrote in
>news:0e3201c37c5b$efc18090$(E-Mail Removed):
>
>> I need to create a copy of a record in Table 2 and all
>> related records from the tables beneath table 2 and
>> assign the data to another record in Table 1, while
still
>> maintaining the original data and assignments.
>>
>
>No you don't. I suggest you read up on database design
and something on
>relational theory and "normalisation". There just isn't
a valid reason for
>copying fields from one table to another.
>
>Best wishes
>
>
>Tim F
>
>.
>

You need to look at normalizing your database. Duplicating
records to change just one value in the record is BAD
design and BAD technique and goes against the traditional
relational database design theory. The subject is to
complicated to address here, but there are many good
reference materials to help!

You might try Database Systems by Connolly and Begg.

That said, without knowing more about what your trying to
accomplish with your database, it's hard for me to make
any solid recommendations but you might try adding a table
with the users id and whatever record value you would use
to tie the two together.

If you have the time, you might want to readup on
relational database design and data normalization.

Hope this helps!

Kevin
>-----Original Message-----
>I have a database with the following structure:
>
>Table 1
>++--Table 2
>++++--Table 3
>++++++--Table 4
>++++++--Table 5
>
>I need to create a copy of a record in Table 2 and all
>related records from the tables beneath table 2 and
>assign the data to another record in Table 1, while still
>maintaining the original data and assignments.
>
>Table 2 is the many side of table 1.
>Table 3 is the many side of table 2.
>Table 4 & table 5 are both the many side of table 3.
>
>Any help would be appreciated.
>Thanks!
>.
>

I've had a similar need where I have a production line
with associated processing steps, labor information,
equipment setup instructions, etc. Often much of the
information is similar to a line that has already been set
up. I use a make table query to transfer the details to
temporary 'copy' tables, then use an update query to
change the appropriate fields from the source line to the
copy line, then use append queries to copy the modified
table info back to the main tables. Its cumbersome, but it
works solid.

Anybody got a better approach? Is there a 'copytree'
with 'replace' option floating around undocumented?

>-----Original Message-----
>Perhaps there is a different way to do this but...
>I need to duplicate a record to assign it to multiple
>users.
>
>Here are the specifics:
>An instructor (table 1)
>Creates an educational map (table 2)
>That contains units (table 3)
>That contains content and standards (table 4 & 5)
>
>A group of instructors got together this summer and
>created a fake "instructor" so they could work together
>on these lesson designs. Now each instructor wants the
>design information in "their" profile (table 1) so they
>can make their own individual changes and customize the
>template that they created in a workshop.
>
>It may not be good normalization practice but this is
>what I need. I can do it record by record but that is
>tedious. I simply want to copy a record and all related
>records to another area.
>
>Hope this paints a better picture of what I need.
>Thanks for your advice.
>-John
>
>>-----Original Message-----
>>"John" <(E-Mail Removed)> wrote in
>>news:0e3201c37c5b$efc18090$(E-Mail Removed):
>>
>>> I need to create a copy of a record in Table 2 and all
>>> related records from the tables beneath table 2 and
>>> assign the data to another record in Table 1, while
>still
>>> maintaining the original data and assignments.
>>>
>>
>>No you don't. I suggest you read up on database design
>and something on
>>relational theory and "normalisation". There just isn't
>a valid reason for
>>copying fields from one table to another.
>>
>>Best wishes
>>
>>
>>Tim F
>>
>>.
>>
>.
>

"John" <(E-Mail Removed)> wrote in
news:039c01c37c7e$f518e9c0$(E-Mail Removed):

> Now each instructor wants the
> design information in "their" profile (table 1) so they
> can make their own individual changes and customize the
> template that they created in a workshop.
>

If I understand you correctly, there is a many:many relationship between
Instructors and Maps.. so you need a new Creates table to manage that
relationship.

If an Instructor creates a new Unit to go in his Map, then he just creates
a new Units record. I still see no reason to go round multiplying records
just for the hell of it.

B Wishes


Tim F

There is a one to many relationship between the
instructor and their created maps. I simply want to
duplicate the map and all related data and assign it to
another instructor so he/she can customize the map for
their classroom. In the end, all the maps are unique and
the data is normalized. It simply saves the instructor
from "re-keying" the template that was created by a group
of teachers for that purpose.

Thanks,
John

>-----Original Message-----
>"John" <(E-Mail Removed)> wrote in
>news:039c01c37c7e$f518e9c0$(E-Mail Removed):
>
>> Now each instructor wants the
>> design information in "their" profile (table 1) so
they
>> can make their own individual changes and customize
the
>> template that they created in a workshop.
>>
>
>If I understand you correctly, there is a many:many
relationship between
>Instructors and Maps.. so you need a new Creates table
to manage that
>relationship.
>
>If an Instructor creates a new Unit to go in his Map,
then he just creates
>a new Units record. I still see no reason to go round
multiplying records
>just for the hell of it.
>
>B Wishes
>
>
>Tim F
>
>.
>

Sounds like you're doing exactly what I need to do. I'll
try to use your example and see if I can make it work.
Any chance I could see the structure of your database for
learning purposes? How many related tables do you have?

Thanks,
John

>-----Original Message-----
>I've had a similar need where I have a production line
>with associated processing steps, labor information,
>equipment setup instructions, etc. Often much of the
>information is similar to a line that has already been
set
>up. I use a make table query to transfer the details to
>temporary 'copy' tables, then use an update query to
>change the appropriate fields from the source line to
the
>copy line, then use append queries to copy the modified
>table info back to the main tables. Its cumbersome, but
it
>works solid.
>
>Anybody got a better approach? Is there a 'copytree'
>with 'replace' option floating around undocumented?
>
>>-----Original Message-----
>>Perhaps there is a different way to do this but...
>>I need to duplicate a record to assign it to multiple
>>users.
>>
>>Here are the specifics:
>>An instructor (table 1)
>>Creates an educational map (table 2)
>>That contains units (table 3)
>>That contains content and standards (table 4 & 5)
>>
>>A group of instructors got together this summer and
>>created a fake "instructor" so they could work together
>>on these lesson designs. Now each instructor wants the
>>design information in "their" profile (table 1) so they
>>can make their own individual changes and customize the
>>template that they created in a workshop.
>>
>>It may not be good normalization practice but this is
>>what I need. I can do it record by record but that is
>>tedious. I simply want to copy a record and all
related
>>records to another area.
>>
>>Hope this paints a better picture of what I need.
>>Thanks for your advice.
>>-John
>>
>>>-----Original Message-----
>>>"John" <(E-Mail Removed)> wrote in
>>>news:0e3201c37c5b$efc18090$(E-Mail Removed):
>>>
>>>> I need to create a copy of a record in Table 2 and
all
>>>> related records from the tables beneath table 2 and
>>>> assign the data to another record in Table 1, while
>>still
>>>> maintaining the original data and assignments.
>>>>
>>>
>>>No you don't. I suggest you read up on database design
>>and something on
>>>relational theory and "normalisation". There just
isn't
>>a valid reason for
>>>copying fields from one table to another.
>>>
>>>Best wishes
>>>
>>>
>>>Tim F
>>>
>>>.
>>>
>>.
>>
>.
>

"John" <(E-Mail Removed)> wrote in
news:0d9001c37de8$33273cc0$(E-Mail Removed):

> It simply saves the instructor
> from "re-keying" the template that was created by a group
> of teachers for that purpose.
>
>

Well, it's easy to use a few lines of vba code to look up a record and copy
the field values into the textboxes on the form....


Tim F