Little background:
The place I work at has 3 shifts with 2 crews on each shift that work in different areas of the building. Those two crews change areas every month and occasionally the crews are rearranged.
Problem:
I have been able to create tables, queries and such to reflect the above situation I have queries for each of the crews for each shift that go back to one main table. I have a field for the "New Crew Number" (1 or 2) and one for "Present Crew Number". When the crew are rearranged there is a posting stating the new assignment about two weeks prior the the switch. What I am trying to do is I want to be able to enter the number in the "New Crew Number" field and on a specified date have that data be automatically entered in the "Present Crew Number" field. Is this possible and if so how can it be done.