Copy a record and all related records

  • Thread starter Thread starter John
  • Start date Start date
J

John

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 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.
Click to expand...

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
 
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
 
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?
 
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.
Click to expand...

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
 
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
 
It simply saves the instructor
from "re-keying" the template that was created by a group
of teachers for that purpose.
Click to expand...

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
 
Back
Top