Xtykutl

I have two Opening Types that I am trying to retrieve counts for in my query in MS-Access. As seen in the photo below. When either both of those values are present in my results they will produce the quantity of how many position are in for each.

When one of the opening types has not been selected at all on the respected table it does not show on the query count as seen below.

My SQL is as follows

SELECT tblOpening.fk_OpeningTypeId

    ,Count(tblOpening.Position) AS CountOfPosition

FROM tblOpeningCity

INNER JOIN tblOpening ON tblOpeningCity.OpeningCityID = tblOpening.City

WHERE (

        ((tblOpening.Position) = "Flex Officer")

        AND ((tblOpening.Closed) = No)

        AND (

            (tblOpeningCity.OpeningCity) = "Livermore"

            OR (tblOpeningCity.OpeningCity) = "Pleasanton"

            )

        )

GROUP BY tblOpening.fk_OpeningTypeId;

I have tried changing the join and no luck. Any Help would be appreciated.

Here is some sample data that is similar to what my database would use.

https://drive.google.com/open?id=1X1W-wctcP7SiYIOWx4VYTOh03zddF81r

Just add your condition with an IIF() statement like that and put a Sum() around it:

SELECT tblOpening.fk_OpeningTypeId

,Sum(IIF(tblOpening.Position = "YourCondition", 1, 0)) AS CountOfPosition

FROM tblOpeningCity

INNER JOIN tblOpening ON tblOpeningCity.OpeningCityID = tblOpening.City

WHERE (

    ((tblOpening.Position) = "Flex Officer")

    AND ((tblOpening.Closed) = No)

    AND (

        (tblOpeningCity.OpeningCity) = "Livermore"

        OR (tblOpeningCity.OpeningCity) = "Pleasanton"

        )

    )

GROUP BY tblOpening.fk_OpeningTypeId;

You can accomplish what you want using a LEFT JOIN. MS Access makes it hard to incorporate the difference conditions, so this probably does what you want:

SELECT o.fk_OpeningTypeId, Count(oc.OpeningCityID) AS CountOfPosition

FROM tblOpening as o LEFT JOIN

     (SELECT oc.*

      FROM tblOpeningCity oc

      WHERE oc.OpeningCity IN ("Livermore", "Pleasanton")

     ) as oc

     ON oc.OpeningCityID = o.City

WHERE o.Position = "Flex Officer" AND

      o.Closed = No

GROUP BY o.fk_OpeningTypeId;

It is possible that the filters on o are removing what you want. If so, then conditional aggregation will fix that:

SELECT o.fk_OpeningTypeId, 

       SUM(IIF(oc.OpeningCityID IS NOT NULL AND

               o.Position = "Flex Officer" AND

               o.Closed = No, 1, 0)

          ) AS CountOfPosition

FROM tblOpening as o LEFT JOIN

     (SELECT oc.*

      FROM tblOpeningCity oc

      WHERE oc.OpeningCity IN ("Livermore", "Pleasanton")

     ) as oc

     ON oc.OpeningCityID = o.City

GROUP BY o.fk_OpeningTypeId;

Access Database Sample