Need query to return sum of fields even when Zero

[jonheese]

I've got 3 tables, Clients, Visits and Genders

Clients:
clientID autonumber pk,
genderID number

Visits:
visitID autonumber pk,
clientID number fk,
visitDate Date/Time

Genders:
genderID autonumber pk,
genderText text

I've built a query to display the total number of clients of a certain gender that have Visit entries in a specified year:

SELECT Genders.genderText AS Gender, Count(Clients.clientID) AS ClientCount, Year([visitDate]) AS VisitYear
FROM (Clients INNER JOIN Genders ON Clients.genderID = Genders.genderID) INNER JOIN Visits ON Clients.clientID = Visits.clientID
GROUP BY Genders.genderText, Year([VisitDate]), Clients.genderID
HAVING (((Year([VisitDate]))=[What year?]))
ORDER BY Clients.genderID;

So, if you've got 100 males and 150 females that have visited in 2005, and you specify the year "2005", the query will return two rows:

Gender | ClientCount | VisitYear
 Male  |    100      |   2005
Female |    150      |   2005

However, I also want to show Zero totals. I.e. there is a gender in the Genders table for "Unknown/unreported". If there are no Visits in 2005 for clients whose gender is "Unknown/unreported", I want the query to return a row with a 0 total for this gender:

      Gender      | ClientCount | VisitYear
       Male       |    100      |   2005
      Female      |    150      |   2005
Unknown/unreported|      0      |   2005

I am not all that SQL-savvy, so I'm not sure how to tweak this query to get what I want. Can anyone point me in the right direction?

Regards,
Jon Heese

 

[dhookom]

Place the GenderID back in the query a remove the gender table. Save the query and then create a new query with your query and the Genders table.Join the GenderID fields and set the join property to include all records from the Genders table.

You can use Nz() if you want to display zeros in the query. You can also use the format property of a text box to display a zero rather than null.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[r937]

select Genders.genderText as Gender
     , Count(Clients.clientID) as ClientCount
     , [What year?] as VisitYear
     , Clients.genderID
  from (
       Genders 
left outer 
  join  
    on Genders.genderID
     = Clients.genderID   
       ) 
left outer
  join Visits 
    on (
       Clients.clientID
     = Visits.clientID
   and Year([VisitDate])
     = [What year?]
       )
group 
    by Genders.genderText
     , Year([VisitDate])
     , Clients.genderID
order 
    by Clients.genderID

r937.com | rudy.ca

 

[jonheese]

dhookom:

Your instructions helped a little, but the only part I'm not clear on how to do is where you said "set the join property to include all records from the Genders table". Heh, if I knew how to do that, I wouldn't really need to ask the question... =) Can you be more specific on how to make the join include all records? Thanks.

r937:

The query you posted doesn't seem to check out. I *think* you meant:

<snip>
left outer
  join Clients 
    on Genders.genderID
     = Clients.genderID   
       )
<snip>

(You left of the table for the first join) But that doesn't seem to give me what I want either. Can you take a look at it and let me know if I misinterpreted something? What I got back from the query was like:

      Gender      | ClientCount | VisitYear | genderID
       Male       |      3      |   2003    |    1
       Male       |      1      |   2003    |    1
      Female      |      3      |   2003    |    2
Unknown/unreported|      1      |   2003    |    3

When it should've looked like:

      Gender      | ClientCount | VisitYear | genderID
       Male       |      0      |   2003    |    1
      Female      |      0      |   2003    |    2
Unknown/unreported|      1      |   2003    |    3

Thanks again for all of your help, and I await your responses.

Regards,
Jon Heese

 

[dhookom]

Jon,
To set the join properties, select the join line and right click on it. Open the Join Properties dialog and select the appropriate option.

You can generally double-click the join line to open the properties dialog.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jonheese]

Awesome, thanks. I keep forgetting about the GUI in Access and I try to do everything in SQL/VB...

Regards,
Jon Heese

 

[r937]

if you have

group
by Genders.genderText
, Year([VisitDate])
, Clients.genderID

and you are getting two rows that say Male, then you have two rows in the Genders table that say Male

i think ;-)


r937.com | rudy.ca

 

[jonheese]

r937:

I'm pretty sure that I had exactly that bit in the query, but I'm very sure that there was only one row for "Male" in the Genders table.

What ended up working was dhookom's suggestion of using two queries and joining the first one with the Genders table:

CountGendersByYear:

SELECT Count(Clients.clientID) AS ClientCount, Year([visitDate]) AS VisitYear, Clients.genderID
FROM Genders INNER JOIN (Clients INNER JOIN Visits ON Clients.clientID = Visits.clientID) ON Genders.genderID = Clients.genderID
GROUP BY Year([visitDate]), Clients.genderID
HAVING (((Year([visitDate]))=[What year?]))
ORDER BY Clients.genderID;

CountGendersByYearAll:

SELECT Genders.genderText AS Gender, CountGendersByYear.ClientCount
FROM Genders LEFT JOIN CountGendersByYear ON Genders.genderID = CountGendersByYear.genderID
GROUP BY Genders.genderText, CountGendersByYear.ClientCount, Genders.genderID
ORDER BY Genders.genderID;

Thanks for your help anyway!

Regards,
Jon Heese