Linking Tables (2003)

Topic

New Reply

Hello,
Some Background: I have a Company and a Job title that have Competencies (Takes initiative, etc). The competencies table has companyID and JobtitleID (as well as competencyID and competencyName) that works fine. I can add competencies based on the company and jobtitle. I have an employee table that has a competencyID field, as well as a companyID and jobtitleID (and understood is the empID)

The question: When I add an employee, I want the competencies to be associated with that employee based on his/her company and jobtitle without having to assign each competency manually to the employee record. Is this possible?

Reply | Quote

Replies

Should it be possible to change the CompetencyID of an employee after it has been assigned automatically based on CompanyID and JobTitleID?

If not, you shouldn’t have a CompetencyID field in the Employees table, since it is derived information. You can create a query based on the Employees and Competencies tables, joined on CompanyID and JobTitleID that returns employee data together with the associated competency data. This query can be used as record source for forms and reports.

If yes, you could put combo boxes for CompanyID and JobTitleID on the Employees form, and put code in the After Update event of the combo boxes to set CompetencyID. The user will be able to modify it afterwards.

Note: can an employee have only one CompetencyID?

Reply | Quote

Hans, I thought I’d send you the structure of the database that I fnally got to work with your help. Maybe someone else will benefit from my lesson and as a way of saying “thank you”.

Reply | Quote

Thanks. I took a quick look at your database. I wondered about the table named TBL_Employee Alias Copy – what is its purpose? Shouldn’t the Reports To field refer back to TBL_Employee?

Reply | Quote

Hans, You are correct. It’s purpose is to link back to the Employee table and the “alias” was my way of making a copy to which to refer back. I don’t know how to use the same table twice, so this was my solution. It’s just a copy of the Employee table.

Reply | Quote

The problem with using a physical copy of the table is that the same data will be stored twice, and you’d have to be very careful to keep the two tables synchronized.

You can add the same table twice in the Relationships window – select Relationships | Show Table or click the Show Table button in the toolbar. This will only create a visual duplicate, not a physical one. You can then create a link between ReportsTo in the original instance and EmployeeID in the new one.

Reply | Quote