Copy Multiple Tables with Relationships

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...

--
Message posted via http://www.accessmonster.com

On Mon, 06 Dec 2004 16:38:00 GMT, "dang nguyen via AccessMonster.com"
wrote:

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.


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

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

--
Message posted via http://www.accessmonster.com

On Tue, 07 Dec 2004 00:40:52 GMT, "dang nguyen via AccessMonster.com"
wrote:

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

The reason why I'd like to copy it is so that I can track changes from year to year.

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?

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