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

[soundman87]

I have two Opening Types (Future & Current) that I am trying to retrieve counts for in my query in MS Access as seen in the image below.


When either of those values/Terms are present in my results they will produce the quantity of how many position are in for each.




When one of the opening types values/terms 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 AS [Opening Type], Count(tblOpening.Position) AS [Walnut Creek Flex Officer]
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 also attached a Database with data similar to what the actual database has so that you can see how it is setup.

Any help would be greatly appreciated.

I know it can be a hassle to write out everything, but I am new to SQL so please no short hand.

[June7]

There must be records to produce output.

Options:

1. dummy record in primary data table for each Type, unfortunately, each dummy record would need to have the data matching the query filter criteria and if the criteria changes, the dummy records must be changed

2. domain aggregate function expressions in textboxes on report, if there are 20 Types then there would be 20 textboxes - I use this technique to make sure I have 12 'rows' for 12 months on report

3. VBA writing summary data to a 'temp' table

[Micron]

Was thinking that an IIF expression in a calculated querry field might work to return a zero where there are no "opening types". However, after looking at the database I'm somewhat confused as to what's going on. Giving table names aliases (captions) so that they have the same name in table view makes no sense to me and only serves to muddle the picture. Also, there is a join between a multi value field and an autonumber field (many of us here avoid mvf's like the plague) plus there just aren't records for all of the opening types with the given criteria.

Also this

When one of the opening types values/terms has not been selected at all on the respected table

suggests you're working in tables, which you should not be.

As june7 is basically saying, there's no elegant solution for this; which begs the question: why do you want to see data for conditions that don't exist? In db practice, the lack of data is usually accepted as such and needs no further embellishment.

[ssanfu]

- I did not see any MVFs, but there are 4 look up fields.

- There are several fields with a special character (the dash) in the name (use the underscore instead).

- And there are 3 fields that have multiple values in them (ie not normalized):
"PositionRequirements" (Must have valid DL, D4L cert, clean MVR)
"ShiftDays" (Fri, Sun, Thursday)
"ShiftTimes" (3pm - 11pm, 2pm- 10pm)

I executed the query, which returned a count of 4 for Walnut Creek Flex Officer, for a future hire.
Then I looked at the data; could not find any positions for Flex Officer for "Livermore" OR "Pleasanton". very strange.....



Might need to revisit the dB design...

[Micron]

You're right. I mis-spoke (can I say that when it was typing?). I saw a 1 in the query instead of text and when "MVF"!
Was a short night last night. That's my excuse and I'm sticking to it.