Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Unanswered: Best Way to Query?

    Hi All,

    I have a database where employees have four specialties. I have one table with employee info containing four ID fields for each specialty. The specialties (name and ID numbers) are in another four tables (Specialties 1-4). I then have a query to link the employees and specialties from the two tables using the specialties ID number. I could only make it work using the four tables. Is this the best way to go?

    B/c now I need to create a search form where I can let users search the four specialty fields to find staff they can work with. For example, if someone was looking for a facilitation expert they could pull "Facilitation" from a drop down list and then see those staff that are "facilitation experts". Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would do it in three tables, although it can be argued you can do it in two...

    I would have your first table roughly of the structure:

    tblEmployees
    --------
    employee_id
    employee_name

    tblSpecialties
    ---------
    specialty_id
    description

    tblEmpSpecialties
    ----------
    emp_specialty_id
    employee_id
    specialty_id


    Query usage:

    SELECT *
    FROM tblEmployees INNER JOIN tblEmpSpecialties ON tblEmployees.employee_id=tblEmpSpecialties.employe e_id
    WHERE tblEmpSpecialties.specialty_id = WhateverSpecialtyIDYouNeedHere
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2004
    Posts
    20
    I kind of see where you're going with this but I'm having trouble trying to visualize how to physically make the joins in the query (if I even have to) based on the new empl_specialty_id and the four specialty IDs that exist for each staff member. Attached is what I currently have if that helps. Thanks in advance.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sorry, I can't download your database here...

    There would be four entries for each employee in the employeeSpecialties table.

    Relationship would look like this:

    Code:
    tblEmployees         tblEmployeeSpecialties        tblSpecialties
    ------------           -------------------          -------------
    name                     specialty_id           <------specialty_id
    employee_id   ----->employee_id                     description
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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