Showing a group with 0 values.

[digitalxni]

Evening All,

I'm currently in the process of creating a database contains data about people. I have to produce a report that counts the amount of people that fall into specific age groups. I've managed to do this but what I also want is to show all the groups even if they have a zero count. I've tried adding '0' and '<>0' in the criteria field but to no avail.

Any ideas?

Thanks,

dx.

 

[boblarson]

If it is a field that returns the count, the criteria is simply:

<>0

no quotes.

 

[digitalxni]

That isn't working. Let me explain this better!

In my query I have 3 fields: Date of Birth (count), a function to make the age groups (group by) and a field which has a yes/no datatye which is set to show only values where this is true (group by). I think it is the third yes/no field which is causing all the problems.

 

[jdraw]

That isn't working. Let me explain this better!

In my query I have 3 fields: Date of Birth (count), a function to make the age groups (group by) and a field which has a yes/no datatye which is set to show only values where this is true (group by). I think it is the third yes/no field which is causing all the problems.

Perhaps you could show us your query and function.

 

[WayneRyan]

DX,

You have to take your "original" query --> Query1:

Select Age As TheAge,
       Count(*) As HowMany
From   YourTable
Group By Age

Then you need to create a table of "years" with the numbers 1 to MaxAge (Call it tblAges).

This table will force an entry for each possible year. The Left Join will ensure that each row
of tblAges is shown in the final query.

Then join it with another query:

Select tblAges.Age,
       Nz(Query1.HowMany, 0)
From   tblAges Left Join Query1 On
         tblAges.Age = Query1.TheAge
Order by tblAges.Age

Then, All of your ages will either have a count of 0 or
they'll have their actual count.

Wayne

 

[digitalxni]

Here is my current query:

SELECT Count(Child.DoB) AS CountOfDoB, AgeGroup([DoB]) AS AgeGrps, Child.AccessedThisQuarter
FROM Child
GROUP BY AgeGroup([DoB]), Child.AccessedThisQuarter, DateDiff("yyyy",[DoB],Now())+Int(Format(Now(),"mmdd")<Format([DoB],"mmdd"))
HAVING (((Count(Child.DoB))<>0) AND ((Child.AccessedThisQuarter)=True));

And my function:

Public Function AgeGroup(DoB As Date) As String
Dim intAge As Integer
intAge = DateDiff("yyyy", [DoB], Now()) + Int(Format(Now(), "mmdd") < Format([DoB], "mmdd"))
Select Case intAge
Case Is < 1
AgeGroup = "A) Under 1"
Case 1
AgeGroup = "B) 1 Year"
Case 2
AgeGroup = "C) 2 Years"
Case 3
AgeGroup = "D) 3 Years"
Case 4
AgeGroup = "E) 4 Years"
End Select
End Function

The function was found via searching.

This function with the above query successfully puts the people in the Child table into the age groups when AccessedThisQuarter is true. It's just that I want to show all the age groups even if there is a 0 value.