multiple query help

[dkeeper09]

Hey guys.



I am going to try and explain my problem the best I can. Any help is greatly appreciated.

I have some tables, employees, assets, terminated assets, and terminated employees. I have created queries for these tables as well.

I have a checkbox column in my employee table. So when it is unchecked, the employee is removed and added to the terminated employee table.

My problem is the employee is still being tied to the assets. Each employee can have multiple assets. When i move the employee to the terminated table, i want the employee's name to be removed from the asset table and moved to terminated assets. I don't want to whole entry deleted, because another employee will use that asset. Is there a way to get it to just remove the employee's name from the asset it is tied to?

I hope this makes sense. Like i said, i appreciate any help that can be given. Thank you!

[jzwp11]

First of all, you should not have the terminated assets or terminated employees tables. One of the primary rules is that similar data should be in 1 place. Having two employee tables or two asset tables violates that rule. It would be better to have a field in the asset table and a field in the employee table that designates that the asset/employee is no longer active. You can use a simple yes/no field. I typically use a date field since it yields a little more information about when the asset or employee was inactivated/terminated. You can use that field to filter your queries. It also sounds like you are missing a table since you say that an asset can go to another person when the employee who used the asset is terminated. Since an asset can be used by many people (over time), it describes a one-to-many relationship. Since you also said that a person can have many assets, that describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (employees & assets in your case), you have a many-to-many relationship which requires a junction table.

tblEmployees
-pkEmpID primary key, autonumber
-txtFName
-txtLName
-dteTerminated (termination date)

tblAssets
-pkAssetID primary key, autonumber
-txtAssetName
-dteTerminated


tblEmployeeAssets (the junction table)
-pkEmpAssetID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkAssetID foreign key to tblAssets
-dteEffective (effective date--when the asset was assigned to the person)

[dkeeper09]

i will definitely try this out. thanks a bunch!

[jzwp11]

You're welcome. Please post back with any questions