MSAccess with a WHERE Clause to only affect one of the LEFT JOINS

Question

I have a 'busy' MSAccess SQL Query which I want to display a full set of results.

In the main table 'JobsMain' a result is either a Maintenance Job or it is not. The following query (MSAccess Relationships: http://m-ls.co.uk/ExtFiles/SQL-Relationship.png) pulls up ALL the data from both sides as you can see:

SELECT   JobsMain.JobNo,
         JobsMain.JobName,
         JobsMain.IsMaintenance,
         GroupHoursEntry.TimeStamp,
         Groups.GroupName,
         GroupMonthlyRates.MonthlyRate,
         Count(MaintenanceSites.SiteID) AS CountOfSiteID,
         Sum(SiteRates.SiteMonthlySalesValue) AS SumOfSiteMonthlySalesValue,
         GroupHourlyRate.GroupHourlyRate,
         GroupHoursEntry.GroupMenEntry,
         GroupHoursEntry.GroupHoursEntry,
         UnitGang.UnitGangRef,
         JobDetails.NoOfMen,
         JobDetails.HrsOnSite,
         UnitGang_1.UnitGangRef
FROM     (((MaintenanceSites
            RIGHT OUTER JOIN
            (((UnitGang AS UnitGang_1
               RIGHT OUTER JOIN
               ((GroupHoursEntry
                 RIGHT OUTER JOIN
                 (JobsMain
                  LEFT OUTER JOIN
                  JobDetails
                  ON JobsMain.JobNo = JobDetails.JobNo)
                 ON GroupHoursEntry.JobNo = JobsMain.JobNo)
                LEFT OUTER JOIN
                UnitGang
                ON GroupHoursEntry.UnitGangID = UnitGang.UnitGangID)
               ON UnitGang_1.UnitGangID = JobDetails.UnitGangID)
              LEFT OUTER JOIN
              Groups
              ON GroupHoursEntry.GroupID = Groups.GroupID)
             LEFT OUTER JOIN
             SitesAndGroups
             ON Groups.GroupID = SitesAndGroups.GroupID)
            ON MaintenanceSites.SiteID = SitesAndGroups.SiteID)
           LEFT OUTER JOIN
           SiteRates
           ON MaintenanceSites.SiteID = SiteRates.SiteID)
          LEFT OUTER JOIN
          GroupHourlyRate
          ON Groups.GroupID = GroupHourlyRate.GroupID)
         LEFT OUTER JOIN
         GroupMonthlyRates
         ON Groups.GroupID = GroupMonthlyRates.GroupID
GROUP BY JobsMain.JobNo, JobsMain.JobName, JobsMain.IsMaintenance, GroupHoursEntry.TimeStamp, Groups.GroupName, GroupMonthlyRates.MonthlyRate, GroupHourlyRate.GroupHourlyRate, GroupHoursEntry.GroupMenEntry, GroupHoursEntry.GroupHoursEntry, UnitGang.UnitGangRef, JobDetails.NoOfMen, JobDetails.HrsOnSite, UnitGang_1.UnitGangRef
HAVING   (((JobsMain.JobNo) = 2156
           OR (JobsMain.JobNo) = 2157));

So if one of the JobsMain results is a Maintenance job then one part of the data is shown and the other side is blank. If it is not a Maintenance job then vica versa. This is exactly what I want as I have a quick generated set of results to work from and a set of calculations are then made off these results.

Originally I was doing an initial simple query to see if it was a Maintenance job or not and then running one of two separate Sub Queries for each of the results of the initial query. This took a long time to generate (3-4 mins). This new query takes seconds to load.

The issue with this new large query is that I need to include a WHERE and HAVING clause to one of the JOINS on the Maintenance Jobs side. If I include a WHERE clause at the end of the query and extend the HAVING query to include the following this works for Maintenance queries but non-Maintenance results are not shown due to these WHERE and HAVING queries preventing them from showing.

SELECT   JobsMain.JobNo,
         JobsMain.JobName,
         JobsMain.IsMaintenance,
         GroupHoursEntry.TimeStamp,
         Groups.GroupName,
         GroupMonthlyRates.MonthlyRate,
         Count(MaintenanceSites.SiteID) AS CountOfSiteID,
         Sum(SiteRates.SiteMonthlySalesValue) AS SumOfSiteMonthlySalesValue,
         GroupHourlyRate.GroupHourlyRate,
         GroupHoursEntry.GroupMenEntry,
         GroupHoursEntry.GroupHoursEntry,
         (GroupHoursEntry.GroupMenEntry * GroupHoursEntry.GroupHoursEntry) AS SumOfMaintenanceHoursEntry,
         UnitGang.UnitGangRef,
         JobDetails.NoOfMen,
         JobDetails.HrsOnSite,
         (JobDetails.NoOfMen * JobDetails.HrsOnSite) AS SumOfJobHoursEntry,
         UnitGang_1.UnitGangRef
FROM     (((MaintenanceSites
            RIGHT OUTER JOIN
            (((UnitGang AS UnitGang_1
               RIGHT OUTER JOIN
               ((GroupHoursEntry
                 RIGHT OUTER JOIN
                 (JobsMain
                  LEFT OUTER JOIN
                  JobDetails
                  ON JobsMain.JobNo = JobDetails.JobNo)
                 ON GroupHoursEntry.JobNo = JobsMain.JobNo)
                LEFT OUTER JOIN
                UnitGang
                ON GroupHoursEntry.UnitGangID = UnitGang.UnitGangID)
               ON UnitGang_1.UnitGangID = JobDetails.UnitGangID)
              LEFT OUTER JOIN
              Groups
              ON GroupHoursEntry.GroupID = Groups.GroupID)
             LEFT OUTER JOIN
             SitesAndGroups
             ON Groups.GroupID = SitesAndGroups.GroupID)
            ON MaintenanceSites.SiteID = SitesAndGroups.SiteID)
           LEFT OUTER JOIN
           SiteRates
           ON MaintenanceSites.SiteID = SiteRates.SiteID)
          LEFT OUTER JOIN
          GroupHourlyRate
          ON Groups.GroupID = GroupHourlyRate.GroupID)
         LEFT OUTER JOIN
         GroupMonthlyRates
         ON Groups.GroupID = GroupMonthlyRates.GroupID
WHERE    (((GroupMonthlyRates.MonthNo) = (Month([JobsMain].[DateStarted]))))
GROUP BY JobsMain.JobNo, JobsMain.JobName, JobsMain.IsMaintenance, GroupHoursEntry.TimeStamp, Groups.GroupName, GroupMonthlyRates.MonthlyRate, GroupHourlyRate.GroupHourlyRate, GroupHoursEntry.GroupMenEntry, GroupHoursEntry.GroupHoursEntry, UnitGang.UnitGangRef, JobDetails.NoOfMen, JobDetails.HrsOnSite, UnitGang_1.UnitGangRef, MaintenanceSites.IsDormant
HAVING   (((JobsMain.JobNo) = 2157)
          AND ((MaintenanceSites.IsDormant) = False));

Now is there something I can do to my query so that I can run this full query, or should I continue to run sub-queries, or perhaps there is a another way of achieving this?

Thank you for your support in this, it is massively appreciated.

Kindest Regards

Paul

Try adding an or clause that looks to see if one of the maintenance-only fields is null. Something like "Where ( (...Existing where clause here...) Or ( [field that is blank on a maintenance job] is null ) ) — Constablebrew, Sep 27 '13 at 18:09
Also, could you post an example of the output, showing one maintenance and one non-maintenance job? — Constablebrew, Sep 27 '13 at 18:12
Hello and thanks for getting back to me. I tried your code and it didn't work. I think that this may be because that the null part of the query still forces for Maintenance part of the Join query which results in the query to return no results for Non-Maintenance jobs — Paul Carruthers, Sep 30 '13 at 08:50
http://m-ls.co.uk/ExtFiles/SQL.png Here is my new SQL (I know the HAVING has been cut off but this is the same as the SQL code above) — Paul Carruthers, Sep 30 '13 at 08:57
http://m-ls.co.uk/ExtFiles/SQLresults.png These are some sample results when I have NO WHERE or HAVING clauses and the results show for each type. I have hidden some of the fields but you can get an idea. If I start applying Maintenance WHERE and HAVING clauses this then doesn't show the Non-Maintennace results, in this case JobNo 1878 Renovation wouldn't show because the database is being asked Maintenance queries when it has no maintenance data — Paul Carruthers, Sep 30 '13 at 09:05
You can't compare a value to null, you need to use the function `ISNULL(MaintenanceSites.IsDormant) = true`. (Likewise in the Where clause.) — Constablebrew, Sep 30 '13 at 17:57

MSAccess with a WHERE Clause to only affect one of the LEFT JOINS

0 Answers0