How to Return a value of Zero Value in Count Query in MS Access?












1















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.



Opening Types



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.



SQL Results



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










share|improve this question

























  • Do you have a table of possible opening types? Perhaps tblOpeningTypes?

    – Lee Mac
    Nov 20 '18 at 23:03











  • Possible duplicate of How to Return a value of Zero for null Value in Count Query in MS-Access?

    – Lee Mac
    Nov 20 '18 at 23:13











  • It is but no one was continuing to respond to my updates.

    – soundman87
    Nov 21 '18 at 0:20











  • There is a link of a sample database that you should be able to download to see what I am working with. If you take a look at that, it might be easier to see where I can update the changes. Take a look at the query

    – soundman87
    Nov 21 '18 at 0:22
















1















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.



Opening Types



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.



SQL Results



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










share|improve this question

























  • Do you have a table of possible opening types? Perhaps tblOpeningTypes?

    – Lee Mac
    Nov 20 '18 at 23:03











  • Possible duplicate of How to Return a value of Zero for null Value in Count Query in MS-Access?

    – Lee Mac
    Nov 20 '18 at 23:13











  • It is but no one was continuing to respond to my updates.

    – soundman87
    Nov 21 '18 at 0:20











  • There is a link of a sample database that you should be able to download to see what I am working with. If you take a look at that, it might be easier to see where I can update the changes. Take a look at the query

    – soundman87
    Nov 21 '18 at 0:22














1












1








1








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.



Opening Types



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.



SQL Results



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










share|improve this question
















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.



Opening Types



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.



SQL Results



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







sql ms-access






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 21 '18 at 0:17







soundman87

















asked Nov 20 '18 at 22:45









soundman87soundman87

82




82













  • Do you have a table of possible opening types? Perhaps tblOpeningTypes?

    – Lee Mac
    Nov 20 '18 at 23:03











  • Possible duplicate of How to Return a value of Zero for null Value in Count Query in MS-Access?

    – Lee Mac
    Nov 20 '18 at 23:13











  • It is but no one was continuing to respond to my updates.

    – soundman87
    Nov 21 '18 at 0:20











  • There is a link of a sample database that you should be able to download to see what I am working with. If you take a look at that, it might be easier to see where I can update the changes. Take a look at the query

    – soundman87
    Nov 21 '18 at 0:22



















  • Do you have a table of possible opening types? Perhaps tblOpeningTypes?

    – Lee Mac
    Nov 20 '18 at 23:03











  • Possible duplicate of How to Return a value of Zero for null Value in Count Query in MS-Access?

    – Lee Mac
    Nov 20 '18 at 23:13











  • It is but no one was continuing to respond to my updates.

    – soundman87
    Nov 21 '18 at 0:20











  • There is a link of a sample database that you should be able to download to see what I am working with. If you take a look at that, it might be easier to see where I can update the changes. Take a look at the query

    – soundman87
    Nov 21 '18 at 0:22

















Do you have a table of possible opening types? Perhaps tblOpeningTypes?

– Lee Mac
Nov 20 '18 at 23:03





Do you have a table of possible opening types? Perhaps tblOpeningTypes?

– Lee Mac
Nov 20 '18 at 23:03













Possible duplicate of How to Return a value of Zero for null Value in Count Query in MS-Access?

– Lee Mac
Nov 20 '18 at 23:13





Possible duplicate of How to Return a value of Zero for null Value in Count Query in MS-Access?

– Lee Mac
Nov 20 '18 at 23:13













It is but no one was continuing to respond to my updates.

– soundman87
Nov 21 '18 at 0:20





It is but no one was continuing to respond to my updates.

– soundman87
Nov 21 '18 at 0:20













There is a link of a sample database that you should be able to download to see what I am working with. If you take a look at that, it might be easier to see where I can update the changes. Take a look at the query

– soundman87
Nov 21 '18 at 0:22





There is a link of a sample database that you should be able to download to see what I am working with. If you take a look at that, it might be easier to see where I can update the changes. Take a look at the query

– soundman87
Nov 21 '18 at 0:22












2 Answers
2






active

oldest

votes


















1














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.






share|improve this answer


























  • Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

    – soundman87
    Nov 21 '18 at 0:15



















0














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





share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53402718%2fhow-to-return-a-value-of-zero-value-in-count-query-in-ms-access%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    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.






    share|improve this answer


























    • Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

      – soundman87
      Nov 21 '18 at 0:15
















    1














    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.






    share|improve this answer


























    • Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

      – soundman87
      Nov 21 '18 at 0:15














    1












    1








    1







    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.






    share|improve this answer















    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.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 21 '18 at 15:21

























    answered Nov 20 '18 at 23:07









    Olivier Jacot-DescombesOlivier Jacot-Descombes

    68.3k890140




    68.3k890140













    • Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

      – soundman87
      Nov 21 '18 at 0:15



















    • Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

      – soundman87
      Nov 21 '18 at 0:15

















    Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

    – soundman87
    Nov 21 '18 at 0:15





    Hello Oliver. Thank you for your response. I hate to ask you this but I am really new to SQL. Would you be able to expand on some of the short hand you used.

    – soundman87
    Nov 21 '18 at 0:15













    0














    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





    share|improve this answer




























      0














      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





      share|improve this answer


























        0












        0








        0







        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





        share|improve this answer













        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 20 '18 at 23:11









        Lee MacLee Mac

        4,77031541




        4,77031541






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53402718%2fhow-to-return-a-value-of-zero-value-in-count-query-in-ms-access%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            How to change which sound is reproduced for terminal bell?

            Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

            Can I use Tabulator js library in my java Spring + Thymeleaf project?