Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Table Relationships

Discussion in 'Information Technology' started by tvh, Nov 5, 2005.

  1. tvh

    tvh
    Expand Collapse
    Guest

    I have three fields in a table ("tblwodLeadTechnician", "tblwodAssistant" and
    "tblwod2ndAssistant") that are linked to one field in another table
    ("tbleiEmployeeID"), however, I don't think I have the relationships set up
    correctly because I'm not able to do many queries in regards to the Employee
    table. The reason I have it set up this way is because ANY of our employees
    can realistically fill EITHER of the three positions on any given day. And
    it does change daily. I'll post the SQL to see if anyone has a suggestion to
    correct the relationships.

    SELECT tblWorkOrderDetail.[tblwodWorkOrder#]
    FROM tblEmployees INNER JOIN tblWorkOrderDetail ON
    (tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwod2ndAssistant) AND
    (tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodAssistant) AND
    (tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodLeadTechnician);

    Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Looks like you have a 3-field relationship, where you need 3 relationships.

    Choose Relationships on the Tools menu.
    If you see 3 lines joining the 2 tables, then delete 2 of the lines.
    Then drag 2 more copies of tblEmployees into the Relationships window.
    Access will alias them as tblEmployees_1 and tblEmployees_2.
    Create the relations to these extra copies.
    You now have 3 relations, instead of a 3-field relation.

    In a similar way, add 3 copies of tblEmployees to your query.
    Each one should have only one line to a fields in tblWorkOrderDetail.

    Unless all 3 possitions are always filled, you will also need to
    double-click these lines in your query, and turn the into outer joins.
    Access will pop up a dialog giving you 3 choices, and you can choose:
    All records from tblWorkOrderDetail and and matches from ...

    But the best solution would be to use another table instead of the 3 joins.
    Sooner or later, there will be other positions as well as those 3, and your
    existing structure does not handle it. Therefore:
    1. Create a table of the possible roles a worker can have.
    It might have just one text field named (say) RoleID. And so you would have
    records like this:
    Assistant
    Lead Technician
    2nd Assistant

    2. Remove the 3 existing fields in tblWorkOrderDetail

    3. Add a table to record the people involved in a work order record, and
    their roles. This table would have 3 fields:
    WorkOrderDetailID foreign key ot tblWorkOrderDetail
    EmployeeID foreign key to tblEmployee
    RoleID foreign key to tblRole.
    You can now have as many or as few workers as you need for any work order
    detail record, in whatever roles are needed. You won't have to redesign the
    structure to cope with future roles, and you don't have multiple relations
    between pairs of tables.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "tvh" <tvh@discussions.microsoft.com> wrote in message
    news:4732FFEE-B247-4538-B41C-89A096A06B6A@microsoft.com...
    >I have three fields in a table ("tblwodLeadTechnician", "tblwodAssistant"
    >and
    > "tblwod2ndAssistant") that are linked to one field in another table
    > ("tbleiEmployeeID"), however, I don't think I have the relationships set
    > up
    > correctly because I'm not able to do many queries in regards to the
    > Employee
    > table. The reason I have it set up this way is because ANY of our
    > employees
    > can realistically fill EITHER of the three positions on any given day.
    > And
    > it does change daily. I'll post the SQL to see if anyone has a suggestion
    > to
    > correct the relationships.
    >
    > SELECT tblWorkOrderDetail.[tblwodWorkOrder#]
    > FROM tblEmployees INNER JOIN tblWorkOrderDetail ON
    > (tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwod2ndAssistant) AND
    > (tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodAssistant) AND
    > (tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodLeadTechnician);
    >
    > Thanks!
     

Share This Page