returning null counts in a query

[postmanphat]

I've written a query that returns counts against a list of 'issues'. However the query only returns the counts if it is > 0. Is there a way of returning null values in a query?

e.g. lets say I have five issues A, B, C, D and E. Issues A, B and D have been picked many times but nobody has issue C or E, so my query results might look like this:

A - 45
B - 23
D - 38

But I want it to look liek this:

A - 45
B - 23
C - 0
D - 38
E - 0

any help would be most appreciated.

Thanks in advance.

 

[r937]

do you have a table for these issues? then use a LEFT OUTER JOIN

if not, why not?

;-)

r937.com | rudy.ca

 

[postmanphat]

I like your style r937...

Yes I do have a table for these issues. And I've tried that but 'tis still just showing results for those it has a value for.....

 

[r937]

select issues.issue
     , count(picks.issue) as countPicks
  from issues
left outer
  join picks
    on picks.issue = issues.issue
group
    by issues.issue

r937.com | rudy.ca

 

[dhookom]

postmanphat,
Consider posting your SQL view when you are having issues.

Duane MS Access MVP

 

[postmanphat]

OK I've now got no problem viewing null counts for all issues - its just that I then need to split which ones were issued to males and females and this is where the trouble is.

Here is my query for showing counts of all issues with nulls being returned:

http://img227.imageshack.us/img227/4907/doesworkua3.jpg

but as soon as I try to just show those issued to males, it stops returning null values:

http://img222.imageshack.us/img222/1622/doesntworkqv5.jpg

Pfffft.

 

[r937]

go to the SQL window, and edit the SQL

remove the condition from the WHERE clause, and add it to the ON clause

select issues.issue
     , count(picks.issue) as countPicks
  from issues
left outer
  join picks
    on picks.issue = issues.issue
   [red]and picks.gender = 'm'[/red]
group
    by issues.issue

sorry i did not use your query to demonstrate, but you haven't posted it yet

;-)

r937.com | rudy.ca

 

[postmanphat]

then it just says "Join Expression not supported"...

I'm gonna cry.

 

[PHV]

Please, post your actual SQL code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

change this --

left outer
join xxx
on xxx.bar = yyy.foo
and xxx.gender = 'm'

to this --

left outer
join xxx
on (
xxx.bar = yyy.foo
and xxx.gender = 'm'
)

if that doesn't work either, you may want to declare and save a query which pulls out just the males, and do your LEFT OUTER JOIN to that

please post your actual sql code


r937.com | rudy.ca

 

[postmanphat]

SQL for finding Number of males with the various issues:

SELECT tblDropIn.IssueID, tblIssues.IssueCode, tblIssues.Issue, Count(tblContacts.GENDER) AS Males
FROM tblIssues INNER JOIN (tblContacts INNER JOIN tblDropIn ON tblContacts.ContactID = tblDropIn.ContactID) ON tblIssues.IssueID = tblDropIn.IssueID
WHERE (((tblContacts.GENDER)="m"))
GROUP BY tblDropIn.IssueID, tblIssues.IssueCode, tblIssues.Issue;

Like I say, it is producing a list that doesn't show all the null counts

 

[lespaul]

you still need a left outer join into the table that lists all the different issues....

SELECT D.IssueID, I.IssueCode, I.Issue, C.Gender, COUNT(*)
FROM tblIssues I
LEFT OUTER JOIN tblDropIn D ON I.IssueID = D.IssueID
INNER JOIN tblContacts C ON D.ContactsID = C.ContactsID
GROUP BY D.IssueID, I.IssueCode, I.Issue, C.Gender

By including the Gender in the SELECT portion and just counting them all and grouping by gender you'll get each issue split into F or M....you may need to swap the tblIssues and the tblDropIn, I can't tell which of these tables has the entire list....

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[postmanphat]

I've done it!

Thanks for all your help!