Solved: Move a matching record

[debauch]

Hello,

I am an Access newb. but it more useful than Excel to maintain data. My question is, can you have a record move to table 3, if it finds a match in table 1 and table 2?

In more detail, I will have an active users table, a terminated user table (3), and a table that gets a new feed of terminated users(2). I want the 'feed' to pull the active users(1) (by employee ID) and put them into the terminated table??

Would this be done running a macro, or is there a basic query that would do the trick?

[asingh]

Hi,

Attached is a sample data base.

Tables:

1.tbl_active_users: the list of current active users.
2.tbl_terminated_users: list of terminated users. [historical]
3.tbl_terminated_users_fresh_feed: updated/fresh list of terminated users.

Queries: Execute in same sequence.

1. qry_append_new_terminated_users: will append/update those users which are in the fresh terminated list and also in the active users list to the historical terminated users list. [Note at this point the new terminated users still exist in the active users list].

2. qry_update_active_tbl_for_deletion: will update a deletion flag on the active users table, which will help to delete those users from the active users table which have been moved to the terminated users table.

3. qry_delete_terminated_users_frm_tbl_active_users: will delete the terminated users from the active users table.

Primary Key for all tables has been set to Employee_ID.

regards,

asingh

[debauch]

Amazing!

This is perfect. I put all three queries into a single button click, and it works seamlessly. Thank you so much. This will definately help wit the start of our database.