Help with moving Data automatically

[alexmb1]

I have two main tables: 'employees' and 'residences'
I have created several queries including one that lists all the employees leaving in the current year and their residences.
there are two forms, one for each table, where new info will be entered to update the tables or add new records.
the relationship is between [employee] in the 'employee' table and [occupant] in the 'residences' table

Here is my goal:

Currently, there are over 200 employees, each are assigned to a house by the company. in a given year about 80 employees leave and 80 new ones arrive. these new employees will be assigned to the houses of the recently departed employees. in the form based off of the 'employees' table i have a combo box based on the 'turnover' query that lists all the residences of the employees departing in the current year. these would be the ones available to reassign to incoming employees.

in the 'employee' table there is a [departure] field that list the day of the departure. What i have been trying to work out is removing(automatically) the names of the employees that departed on Date()-1 from the 'employees' form. for that i created an update query that just removed them from the list. then i realized that this process disturbed the housing 'turnover' query that derrived its contents from [departure] field in the 'employee' table. that criteria is set to the current year.

Also, when i add a new employee to a new address, the old occupant is still listed on the form at that address because i have not figured out how to move the old employee to the 'archive' table when a new person is assigned to that address.

how can i:

1) find a way to remove an employee from the 'employee' table to the archive table while simultaneously preserving their residence and relavant info in the 'turnover' table (again, this is based on the departure date derrived from the employee's name)

2) is there a way to eliminate a record from the 'employee' table and move it to the 'archive' table when an incoming employee is selected to live at the outgoing employees address while also transferring the [permanent_address] of the outgoing employee to the [past_address] in the archive table

to me is sounds like i need to use some complicated macros that i may need to program on my own. i would ideally like this to be as automatic as possible and maybe work off of an update query. please let me know if that is more info that i need to provide.
thanks

alex

 

[jzwp22]

It sounds like you need at least one other table. Since a residence can have many employees over time, I think you need a junction table like the following:

tblEmpResidences
-pkEmpResID primary key, autonumber
-fkResidenceID foreign key to table that holds a list of the residences
-fkEmpID foreign key to table that holds the employee info
-dteMoveIn date the employee moved into the residence
-dteMoveOut date the employee moved out of the residence

You can use the dteMoveOut date to filter the records in your form. If the dteMoveOut field has a value in it, don't show the record in the form. You can also use that same field to indicate which residences are available for incoming employees.



To distinguish active employees from employees that have left, you can add a simple yes/no field to your employee table. You can use that to filter the info that you view in your forms or you can use that to migrate former employees to your archive table. (I would recommend that you do not delete employee records)

Now to track the address(es) of people who have left the company (i.e. for tax purposes etc.), I would make the assumption that a former employee might have multiple addresses with only one of them being active at any one time. So, you can create another table.

tblEmpAddress
-pkEmpAddrID primary key, autonumber
-fkEmpID foreign key to employee table
-txtAddr
-txtCity
-txtState
-logActive (yes/no field to designate the active address for a former employee)

 

[HiTechCoach]

Moving data scares me! I avoid it . There is just to many things that can go wrong. Also, what if you need it back because you archive the wrong one? Wit, that would never happen. What was I thinking.

I would not move the data to an archive table. I would use a field to flag the record as no longer active. This way you can filter then out when you do not want to see them. This will also preserve all the historical data. Also, with this method there is NO complex macros/VBA code needed.

 

[Atomic Shrimp]

I'm with HiTechCoach. In my experience, instructions to the effect "Delete this - we'll never need it again" are very often followed by requests to the effect "OK. Now, how do we get it back?"

If you've already got a termination date for the records you no longer want, you don't even necessarily need a flag to mark inactive records - just use the termination date to exclude old records from your query results.

 

[alexmb1]

i have never created a junction table before and am not sure i know how to get the foreing keys in the tables like you say. each time i add a field i am getting a combo box.

 

[alexmb1]

so i have been thinking about this and i think the most straightforward approach is to create a query that is made up of all employees that have departed and is grouped by address. that will creat a simple reference for past residents. as for the form i think the easiest way is just to create a yes/no box and then filter by that. how am i able to set the form to only return those records and how to i set the yes/no to base off of the departure date? should i use and IIF statement? as far as the turnover query i was thinking that maybe the query would recognize when it is reassigned and would automatically remove itself. any thoughts?

 

[jzwp22]

I have attached a sample database that illustrates the use of a junction table. Please take a look at the tables, relationships and the frmResidences with its subform based on the junction table. I did use a query for the combo box in the subform to show only active employees when assigning an employee to a residence.