Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Tables (2003)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (2003)

    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?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (2003)

    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".

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (2003)

    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?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •