Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    creating a build (Access03)

    I have a query comprised of the following tables: tblLearners, tblLearnerDepartment, tblTelephone, and tbldepartments.

    In the tblLearnerDepartments there are fields for Department and PerDiem2Unit. In most cases the information is the same for each record. The PerDiem2Unit was necessary because if a person was in the Department "Inservice Education" they actually worked on one of the other units. Illustration

    Department PerDiem2Unit
    ICU ICU
    Inservice Education ICU

    For reports there is a Requested field in the tblDepartment to indicate the required departments. The requested field is a yes/no field.
    What I need is when ICU is selected the people from the Inservice Education department that works in ICU should also be selected.

    Question: Is there a way with a build to pull those from Inservice Education Department that equals the other selected departments. If I select Inservice Education department I get all other departments.

    Here is the current SQL:

    SELECT DISTINCTROW tblTelephone.LearnerID, tblLearnerDepartments.PerDiem2Unit, tblLearners.LastName, tblLearners.Nickname, [LastName] & ", " & [Nickname] AS FullName, tblLearners.Credential, tblLearnerDepartments.Status, tblTelephone.TelephoneType, tblTelephone.[Telephone#], tblTelephone.Extension, tblTelephone.Comments, tblLearners.Inactive, tblDepartments.Requested, tblDepartments.Clinical
    FROM ((tblLearners INNER JOIN tblLearnerDepartments ON tblLearners.LearnerID = tblLearnerDepartments.LearnerID) INNER JOIN tblDepartments ON tblLearnerDepartments.Department = tblDepartments.Department) LEFT JOIN tblTelephone ON tblLearners.LearnerID = tblTelephone.LearnerID
    WHERE (((tblLearners.Inactive)=0) AND ((tblDepartments.Requested)=-1) AND ((tblDepartments.Clinical)=-1))
    ORDER BY tblLearnerDepartments.PerDiem2Unit, tblLearners.LastName, tblTelephone.TelephoneType;

    Thanks for your help. Fay

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: creating a build (Access03)

    I am not sure that I completely clear about this, but it seems to me you need to include two copies of the table tblDepartments.

    One copy is joined to tblLearnerDepartments by the field Department and the other copy joined by PerDiem2Unit.
    Regards
    John



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

    Re: creating a build (Access03)

    Have you tried joining tblLearnerDepartments to tblDepartments on PerDiem2Unit vs Department ?

    ... INNER JOIN tblDepartments ON tblLearnerDepartments.PerDiem2Unit = tblDepartments.Department ...

Posting Permissions

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