Argthtjtr

I have two Opening Types that I am trying to retrieve counts for in my query in MS Access as seen in the image 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.

Here is a copy of my SQL

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 using different types of outer join with no luck.

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

I am new to SQL, please do not use short hand.

Sample Database

Assuming that you have a table tblOpeningType include it into the query and use a left join which shows all the records from the left table. Also, since you apply a where clause to the right table, this would ruin the effect of the LEFT JOIN. Therefore I use a sub-select.

SELECT

    T.OpeningTypeId,

    Count(X.Position) AS CountOfPosition

FROM

    tblOpeningType T

    LEFT JOIN (

        SELECT

            O.fk_OpeningTypeId AS OpeningTypeId,

            O.Position

        FROM

            tblOpening O

            INNER JOIN tblOpeningCity C

                ON O.City = C.OpeningCityID

        WHERE

            O.Position = "Flex Officer" AND

            O.Closed = No AND

            (C.OpeningCity = "Livermore" OR C.OpeningCity = "Pleasanton")

    ) X

    ON T.OpeningTypeId = X.OpeningTypeId

GROUP BY T.OpeningTypeId;

When two tables are joined, INNER JOIN yields results where there is a corresponding record in both tables. LEFT JOIN yields results for all the records of the left table and when records are missing in the right table, the corresponding result columns are filled with NULL.

Example:

Person              City

------              ----

Id Name CityId      Id Name

-- ---- ------      -- -------

1  Joe  10          10 Atlanta

2  Sue  10          20 Boston

3  Tim  30          30 Chicago

A INNER JOIN query:

SELECT c.Id, c.Name AS City, p.Name AS Person

FROM City c INNER JOIN Person p ON c.Id = p.CityId

Result:

Id  City     Person

--  -------  ------

10  Atlanta  Joe

10  Atlanta  Sue

30  Chicago  Tim

With a LEFT JOIN (City is the left table in this query):

SELECT c.Id, c.Name AS City, p.Name AS Person

FROM City c LEFT JOIN Person p ON c.Id = p.CityId

Result:

Id  City     Person

--  -------  ------

10  Atlanta  Joe

10  Atlanta  Sue

20  Boston   <NULL>

30  Chicago  Tim

Now, your query shows an additional difficulty. It says WHERE tblOpening.Position = "Flex Officer" .... This disallows tblOpening.Position to be NULL in the result set (even if the column is not in the select-list), as a LEFT JOIN with tblOpeningType would require. Therefore I use the SELECT with this WHERE-clause as a sub-select. A sub-select is simply a SELECT command enclosed in brackets (SELECT ...) X with a given alias (X) that is used like a table in a surrounding query.

Following my comment, assuming you have a separate table of all possible opening types (e.g. tblOpeningTypes with primary key id), then I would suggest the following:

select

    ot.id, nz(t.cnt, 0) as positions

from

    tblopeningtypes ot

    left join

    (

        select o.fk_openingtypeid, count(o.position) as cnt

        from tblopening o inner join tblopeningcity oc on o.city = oc.openingcityid

        where

            o.position = "Flex Officer" and

            o.closed = no and 

            oc.openingcity in ("Livermore", "Pleasanton")

        group by o.fk_openingtypeid

    ) t

    on ot.id = t.fk_openingtypeid