Copy Multiple Tables with Relationships

  • Thread starter Thread starter dang nguyen via AccessMonster.com
  • Start date Start date
D

dang nguyen via AccessMonster.com

I have a database that employees complete annual assessments on. There are two types of assessments, an initial assessment and an annual review. After an employee completes an initial assessment, I want the ability for them to "Review" their initial assessment so they can make any changes from the previous year. I was thinking about doing this by copying all the tables associated with the initial assessment and giving this review assessment a different id. I am thinking that I would do an append query based on the certain criteria, however, there are relationships with other fields in other tables that are autonumber fields. I am not sure how to copy all these relationships.

Any help please.




the basic structure is

tbl assesssment
-assessment_ID (autonumber)
-Locations_ID
-etc.

tbl locations
-Location_ID (autonumber)
-Location Name
-Assessment_ID
-Details...

tbl observations
-Obseravation_ID (autonumber)
-Location_ID
-Assessment_ID
-Details...
 
I have a database that employees complete annual assessments on. There are two types of assessments, an initial assessment and an annual review. After an employee completes an initial assessment, I want the ability for them to "Review" their initial assessment so they can make any changes from the previous year. I was thinking about doing this by copying all the tables associated with the initial assessment and giving this review assessment a different id. I am thinking that I would do an append query based on the certain criteria, however, there are relationships with other fields in other tables that are autonumber fields. I am not sure how to copy all these relationships.
Click to expand...

Umm... what purpose would be served by creating a new table?? If you
want to review it, simply create a Select query displaying the data to
be reviewed. If they'd like to be able to copy the *data* from a
previous review to this year's review, you can create an Append Query
(or queries) to copy data from the table into itself; no second table
is necessary or appropriate. Just change the date field and don't
append any autonumber ID fields.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Umm... what purpose would be served by creating a new table?? If you
want to review it, simply create a Select query displaying the data to
be reviewed. If they'd like to be able to copy the *data* from a
previous review to this year's review, you can create an Append Query
(or queries) to copy data from the table into itself; no second table
is necessary or appropriate. Just change the date field and don't
Click to expand...

The reason why I'd like to copy it is so that I can track changes from year to year. I've been able to do an append query to copy the "tbl assessment" however, it is linked to two other tables (tbl locations, tbl observations) which are linked by autonumber fields. If I copy "tbl locations", how do I maintain the relationship?

Thanks, dang
 
The reason why I'd like to copy it is so that I can track changes from year to year.
Click to expand...

Storing data - such as a year - in a Tablename is *extremely bad
design*. Don't! It will cause FAR more trouble than benefit.

Instead, if you want to track changes from year to year, store the
year - or the date - in your table as multiple records and use queries
to extract the data for a particular year. You're using a relational
database; use it relationally!
I've been able to do an append query to copy the "tbl assessment" however, it is linked to two other tables (tbl locations, tbl observations) which are linked by autonumber fields. If I copy "tbl locations", how do I maintain the relationship?
Click to expand...

You'll need to recreate the relationship, either manually or with the
CreateRelationship method in VBA code. You can't maintain it
automatically.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top