Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with query: filter on dates in different tables

    Hi folks,

    As the thread title, I have created a report but found my query wasn't bringing back what I wanted.

    Basically I have a table listing company/employer names, another table with people who work at said company/employer, and two other tables one for any enquiries, another for any training and the dates of enquiry/training.

    Another table that is link to the company/employer table lists the last contact someone had with said company/employer.

    Click the link to see the structure of my query.

    http://kevincb.nadsl.net/ReportQuery.jpg

    I am trying to get back the company/employer that had contacted, enquired or training between two dates.

    What seems to be happening is the query is comparing all three dates and grouping them together.

    But I require the query to fetch all the company/employers that had training, and or, fetch all the company/employers that enquired, and or, fetch all the company/employers that we had last contact with between the two dates.

    Hopefully that makes sense.

    What's my best solution for this problem?

    Here's the query syntax:

    Code:
    SELECT dbo_tblEmployer.EmpName, dbo_tblEmployer.Branch, dbo_tblEmployer.Addr4,
    dbo_tblEmployer.Addr5, dbo_tblEmployer.PostCode, dbo_tblEmployer.PostCode2,
    dbo_tblEmployer.OnStop, dbo_tblEmployer.Train2Gain, dbo_tblEmployer.NPTC,
    dbo_tblEngagement.EngageType, dbo_tblEngagement.EngageDate, dbo_tblBArea.BArea,
    dbo_tblNPTCEnquiry.EnquiryDate, dbo_tblNPTCTrained.TrainedDate
    FROM ((dbo_tblBArea RIGHT JOIN ((dbo_tblEmployer LEFT JOIN dbo_tblEngagement
    ON dbo_tblEmployer.EmpID = dbo_tblEngagement.EmpID) LEFT JOIN dbo_tblBAreaLink
    ON dbo_tblEmployer.EmpID = dbo_tblBAreaLink.EmpID) ON dbo_tblBArea.BAreaID = dbo_tblBAreaLink.BAreaID)
    LEFT JOIN (dbo_tblNPTCEmployee LEFT JOIN dbo_tblNPTCEnquiry ON dbo_tblNPTCEmployee.NPTCEmpID = dbo_tblNPTCEnquiry.NPTCEmpID)
    ON dbo_tblEmployer.EmpID = dbo_tblNPTCEmployee.EmpID) LEFT JOIN dbo_tblNPTCTrained ON dbo_tblNPTCEmployee.NPTCEmpID = dbo_tblNPTCTrained.NPTCEmpID
    WHERE (((dbo_tblEngagement.EngageDate) Between [Forms]![frmReports]![txtDate1] And [Forms]![frmReports]![txtDate2])
    AND ((dbo_tblNPTCEnquiry.EnquiryDate) Between [Forms]![frmReports]![txtDate1] And [Forms]![frmReports]![txtDate2])
    AND ((dbo_tblNPTCTrained.TrainedDate) Between [Forms]![frmReports]![txtDate1] And [Forms]![frmReports]![txtDate2]))
    OR (((dbo_tblNPTCEnquiry.EnquiryDate) Between [Forms]![frmReports]![txtDate1] And [Forms]![frmReports]![txtDate2])
    AND ((dbo_tblNPTCTrained.TrainedDate) Between [Forms]![frmReports]![txtDate1] And [Forms]![frmReports]![txtDate2]))
    OR (((dbo_tblNPTCTrained.TrainedDate) Between [Forms]![frmReports]![txtDate1] And [Forms]![frmReports]![txtDate2]));

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by KevCB226
    But I require the query to fetch all the company/employers that had training, and or, fetch all the company/employers that enquired, and or, fetch all the company/employers that we had last contact with between the two dates.
    that sounds like yo might want --

    SELECT ... WHERE ...
    UNION ALL
    SELECT ... WHERE ...
    UNION ALL
    SELECT ... WHERE ...

    as for your current query, when i look at the fine mix of LEFT and RIGHT joins you have, my eyes start to bleed and my brain starts to leak...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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