Multi-Table Append & Delete Queries

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I have 3 tables - tblRecruits, tblEvaluations, and
tblContacts. There is a one-to-many relationship between
tblRecruits and tblEvaluations and a one-to-many
relationship between tblRecruits and tblContacts.

Using a macro, I want to run an append and then a delete
query on all fields of all three tables based on the
GradYear field in tblRecruits. (Specifically, I want all
Class of 2002 Recruits along with all of their evaluations
and contacts appended to archive tables and then deleted
from my active database.) The archive tables are currently
located within my active database. There is a remote
possibility that I would need to access the data in the
future; so I want to keep it. However, I want to move
these Recruits out of my active system. I will build the
query with the parameter [Enter Grad Year] so that this
query can be run annually.

My question:

Can I do this within one append query and one delete query
or do I have to do an append query for each table and a
delete query for each table? I have tried doing it within
one, but cannot get the Append To row to allow different
table destinations.

Any help is appreciated.
 
Hi Melissa,

As far as I know you have to create a separate append and
delete query for each destination table or table to
delete records from.

But, if you are keeping the tables within the same
database, you may want to consider just adding an archive
field to your main table. Then, just exclude the
archived records from forms and reports (Archived <>
True). This would be much easier as you would only have
to periodically run an update query to archive certain
records, and it would also be easier to include the
records in queries/reports now and then if you wish.

Hope this helps.

-Ted Allen
 
Ted:

Thanks for the help. If I add the Archive field (Yes/No
field), then could I use a macro to run a parameter
select query with a parameter to select the Grad Year and
then an update query to set Archive to No?

If that is possible, do you know how I set the value of a
Yes/No field to No?

Thanks again for any ideas.

Melissa
-----Original Message-----
Hi Melissa,

As far as I know you have to create a separate append and
delete query for each destination table or table to
delete records from.

But, if you are keeping the tables within the same
database, you may want to consider just adding an archive
field to your main table. Then, just exclude the
archived records from forms and reports (Archived <>
True). This would be much easier as you would only have
to periodically run an update query to archive certain
records, and it would also be easier to include the
records in queries/reports now and then if you wish.

Hope this helps.

-Ted Allen
-----Original Message-----
I have 3 tables - tblRecruits, tblEvaluations, and
tblContacts. There is a one-to-many relationship between
tblRecruits and tblEvaluations and a one-to-many
relationship between tblRecruits and tblContacts.

Using a macro, I want to run an append and then a delete
query on all fields of all three tables based on the
GradYear field in tblRecruits. (Specifically, I want all
Class of 2002 Recruits along with all of their evaluations
and contacts appended to archive tables and then deleted
from my active database.) The archive tables are currently
located within my active database. There is a remote
possibility that I would need to access the data in the
future; so I want to keep it. However, I want to move
these Recruits out of my active system. I will build the
query with the parameter [Enter Grad Year] so that this
query can be run annually.

My question:

Can I do this within one append query and one delete query
or do I have to do an append query for each table and a
delete query for each table? I have tried doing it within
one, but cannot get the Append To row to allow different
table destinations.

Any help is appreciated.
.
Click to expand...
.
Click to expand...
 
Hi Melissa,

Sorry for the delayed response, I was off last Fri.
Hopefully you will still find this.

Yes, you can add a parameter directly inside an update
query to limit the set of records that would be updated.
To do this, just add the field that you want to compare
the criteria to (such as grad year) and add the criteria
to the criteria line (in your case a parameter), but
leave the "Update To" line blank. Then, for the field
you want to update, enter the value that you want to
update it to. For a yes/no field, you can enter True for
yes and False for no.

Hope that helps.

-Ted Allen
-----Original Message-----
Ted:

Thanks for the help. If I add the Archive field (Yes/No
field), then could I use a macro to run a parameter
select query with a parameter to select the Grad Year and
then an update query to set Archive to No?

If that is possible, do you know how I set the value of a
Yes/No field to No?

Thanks again for any ideas.

Melissa
-----Original Message-----
Hi Melissa,

As far as I know you have to create a separate append and
delete query for each destination table or table to
delete records from.

But, if you are keeping the tables within the same
database, you may want to consider just adding an archive
field to your main table. Then, just exclude the
archived records from forms and reports (Archived <>
True). This would be much easier as you would only have
to periodically run an update query to archive certain
records, and it would also be easier to include the
records in queries/reports now and then if you wish.

Hope this helps.

-Ted Allen
-----Original Message-----
I have 3 tables - tblRecruits, tblEvaluations, and
tblContacts. There is a one-to-many relationship between
tblRecruits and tblEvaluations and a one-to-many
relationship between tblRecruits and tblContacts.

Using a macro, I want to run an append and then a delete
query on all fields of all three tables based on the
GradYear field in tblRecruits. (Specifically, I want all
Class of 2002 Recruits along with all of their evaluations
and contacts appended to archive tables and then deleted
from my active database.) The archive tables are currently
located within my active database. There is a remote
possibility that I would need to access the data in the
future; so I want to keep it. However, I want to move
these Recruits out of my active system. I will build the
query with the parameter [Enter Grad Year] so that this
query can be run annually.

My question:

Can I do this within one append query and one delete query
or do I have to do an append query for each table and a
delete query for each table? I have tried doing it within
one, but cannot get the Append To row to allow different
table destinations.

Any help is appreciated.
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top