Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Need to create view with NULL values as 0

nk1967innk1967in Member Posts: 1
Dears All,
I have database Access2000

I have a table 'classMast' with fields
className
1
2
3
4
5

and I have a view(vwStudDetailsInCurSession) which has fields
className Gender
1 Female
1 Male
1 Male
2 Male
3 Female
4 Male
5 Female
etc...
Note that className = 2 and 4 does not have female students
Now I want to create a view joining both 2 table & view. so that all the classes should count girls even if they don't have female students(Should be shown as 0)

my query

SELECT classMast.className, Count(vwStudDetailsInCurSession.gender) AS CountOfgender
FROM classMast LEFT outer JOIN vwStudDetailsInCurSession ON classMast.className = vwStudDetailsInCurSession.className
WHERE (((vwStudDetailsInCurSession.gender)='Female'))
GROUP BY classMast.className;

it shows only the classes which has females but I want to display all the classes with CountOfgender = 0 where femeales are not there.

thanks in advance
Sign In or Register to comment.