Showing all results with 0 in a query

[digitalxni]

Hey guys.

I have a table that contains data on people including their birthdate. What I want to do is to have a report that groups people into age ranges and show it in a table. I've managed to do this via a function and a query but what I can't seem to do is show the group titles if no one falls into that group.

Here is the SQL query:

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

and the 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

Also on another note I have two tables: carers and children (with a one to many relationship). In the table carers there is a field for the carers ethnic origin and a field for their partners and in the child table there is a field for the childs ethnic origin. What I am trying to do with no success at all is merge all these together to give me a table that resembles the following:

Ethnic Origin Adults Children
-----------------------------------------------
British 2 5
etc.

Any ideas on how to go about this?

 

[Guus2005]

Hey guys.

I have a table that contains data on people including their birthdate. What I want to do is to have a report that groups people into age ranges and show it in a table. I've managed to do this via a function and a query but what I can't seem to do is show the group titles if no one falls into that group.

Here is the SQL query:

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

and the 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"
[COLOR="Red"]Case Else 
AgeGroup = "X) Unknown Years"[/COLOR]
End Select
End Function

Also on another note I have two tables: carers and children (with a one to many relationship). In the table carers there is a field for the carers ethnic origin and a field for their partners and in the child table there is a field for the childs ethnic origin. What I am trying to do with no success at all is merge all these together to give me a table that resembles the following:

Ethnic Origin Adults Children
-----------------------------------------------
British 2 5
etc.

Any ideas on how to go about this?

For your first question, it is marked red in your quote.

The table Carers and Children both have a field named EthnicOrigin?
Your sql statement might look something like this:

select EthnicOrigin, Count(Adults), Count(Children) from Carers inner join Children on Carers.EthnicOrigin = Children.EthnicOrigin Group By EthnicOrigin.

This query doesn't show records where the Ethnic Origin in one table doesn't match the Ethinic Origin in the other table.

If that doesn't work or you don't know how, please post a (zippped) sample database (2003)

HTH