Yes, no, why?
Yes, it is possible to get a record in one table "copied" to a table in
another.
No, there isn't a "field" in a table that will do this for you. You will
need to create multiple queries (one to find and append the correct
record(s), one to delete it from the "old" table), and you will probably
need/want to embed this in code that confirms that the record was added to
table 2 before deleting it from table 1. (Or you could do this all in
code, using UPDATE and DELETE SQL statements.)
Why would you want to? What difference does it make to your business
process or users what table the record is in? In fact, I can imagine
situations in which having records strung out over multiple tables would
make looking up historical information quite a bother!
You've described a "how", as in how you want to accomplish something.
You've not described what business need you are trying to solve, i.e., what
that something is. What will having a record moved from table 1 to table 2
allow you/your users to do?
--
Regards
Jeff Boyce
www.InformationFutures.net
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/