Archiving records part 2

[NWGal]

I know now the best way to archive data from one table, but I need to archive related records in another table. What is the best set up for that. The two tables both have a yes/no field for archived and are related on Team ID. The Team table holds the basic team data for each team, the Assignment table shows each member on the team. I have already set up a form based on a query and am using an unbound combo box to select the team I want to archive. I can now update the Teams table, but I need to also update the assignment table so that the team's members are archived as well.

[HansV]

Is it really necessary to 'archive' assignment records? It depends on how you use the assignments table.
In principle, you can always use a query that joins the teams and assignments tables on TeamID and selects only those records for which the Archived field in the teams table is False.

[NWGal]

Is it really necessary to 'archive' assignment records? It depends on how you use the assignments table.
In principle, you can always use a query that joins the teams and assignments tables on TeamID and selects only those records for which the Archived field in the teams table is False.

Yes, I do because one of my reports is based on a query which gets data from that table, so when I run the report I need it to only have active assignments.
I have the query set up, and it shows me all the right records i.e., Team A has three records, one for each member, all good. However, I'm assuming when I using this query from the form, I assume I will need to click all three records? Is there a way to choose the team and have it update all the records at once?

[HansV]

As I mentioned, you can create a query based on the teams and assignments tables, joined on TeamID, and select only records from the assignments table for which the Archived field in the related record in the teams table is False. You can then use this query as record source for the report.

[NWGal]

As I mentioned, you can create a query based on the teams and assignments tables, joined on TeamID, and select only records from the assignments table for which the Archived field in the related record in the teams table is False. You can then use this query as record source for the report.

AHA! It took me some brain crunching, but I finally figured out what you meant. I fixed it! No archived field on assignment, and actually didn't need a new query at all, I just went to all the pertinent queries and added the Archived field from the teams table and put in the criteria. Now it's working great. Thank you again!

[HansV]

Yep, that's what I meant.