Count number of children of a person

[Khalil Handal]

Hi To all,
I have employees who have children, and the children are in tblChildren that has Primary Key, ChildName, DOB (Birth date).
What I need to have is:
1- Total number of children for a specific employee.
2- Number of children under 18 years old for that employee and
3- Number of children over 18 years old.

How can I do This?



Thank you
Khalil

[ranman256]

SELECT tEmployees.Employee, Count(tChildren.ChildID) AS CountOfChildren
FROM tEmployees INNER JOIN tChildren ON tEmployees.EmpID = tChildren.EmpID
GROUP BY tEmployees.Employee;

[jojowhite]

you may also try this query:

SELECT 
    EmployeeID, 
    EmployeeName,
     DCount("1", "tblChildren", "EmployeeID = " & [EmployeeID]) As CountOfChildren, 
    DCount("1", "tblChildren", "EmployeeID = " & [EmployeeID] & " And DateDiff('yyyy', [DateOfBirth], Date()) < 18") As CountChildUnder18, 
    DCount("1", "tblChildren", "EmployeeID = " & [EmployeeID] & " And DateDiff('yyyy', [DateOfBirth], Date()) >= 18") As CountChildOver18 
FROM YourTableName;

[Welshgasman]

One of those 18s needs an = sign?
I would create a function for the age, as by years I am 71, but not until July this year.

[CJ_London]

the children are in tblChildren that has Primary Key, ChildName, DOB (Birth date).

it may be your terminology but the table also needs a Foreign Key to link to the Primary Key of the employee table

[NoellaG]

you may also try this query:

SELECT 
    EmployeeID, 
    EmployeeName,
     DCount("1", "tblChildren", "EmployeeID = " & [EmployeeID]) As CountOfChildren, 
    DCount("1", "tblChildren", "EmployeeID = " & [EmployeeID] & " And DateDiff('yyyy', [DateOfBirth], Date()) < 18") As CountChildUnder18, 
    DCount("1", "tblChildren", "EmployeeID = " & [EmployeeID] & " And DateDiff('yyyy', [DateOfBirth], Date()) > 18") As CountChildOver18 
FROM YourTableName;

Never use domain functions in queries. In the above query the engine has to do 3 table scans for each result line.
Better use 3 groups queries and link them.

[jojowhite]

I agree, Domain functions can cause slowdown, specially for large number of records.
but it will still be fast if you only have 1000 employees and each employee has 20 childrens.
and the bonus of using Dfunc() is that your query is updateable.

[madpiet]

But if you use an INNER join between Employees and Children tables, any employee without children will be removed from the result set. If you want to keep the Employees without children, you need to use an outer join...

FROM tEmployees LEFT JOIN tChildren...

or you could confuse people and do it this way... <g>

SELECT e.EmployeeID, e.FirstName, e.LastName, c.AgeGroup, COUNT(c.Birthdate) AS ChildCount
FROM Employee e 
LEFT JOIN (
SELECT FirstName
	, EmpParentID
	, BirthDate
	, AgeGroup = CASE WHEN DATEDIFF(year,Birthdate,GETDATE()) < 18 THEN 'Under 18' ELSE 'OVER 18' END  /* yeah, i know it's wrong... it's close enough */
FROM Child ) c
ON e.EmployeeID = c.EmpParentID
GROUP BY e.EmployeeID, e.FirstName, e.LastName, c.AgeGroup;

[Welshgasman]

Could we just not use an IIF() and a function so as to place the age in the correct category?

[madpiet]

yup.

AgeGroup2 = IIF(DATEDIFF(year,Birthdate,GETDATE()) < 18,'Under 18', 'Over 18')

Maybe I've been playing with common table expressions for too long.

[Welshgasman]

yup.

AgeGroup2 = IIF(DATEDIFF(year,Birthdate,GETDATE()) < 18,'Under 18', 'Over 18')

Maybe I've been playing with common table expressions for too long.

As I mentioned before, that is inaccurate, due to month of birth and current month? That is why I suggested a function. Plenty on the net.

Here is one someone else wrote

Public Function AgeYears(ByVal datBirthDate As Date) As Integer
  ' Comments: Returns the age in years
  ' Params  : datBirthDate    Date to check
  ' Returns : Number of years
  ' Source  : Total Visual SourceBook

  On Error GoTo Proc_Err

  Dim intYears As Integer

  intYears = Year(Now) - Year(datBirthDate)

  If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
   ' Subtract a year if birthday hasn't arrived this year
    intYears = intYears - 1
  End If

  AgeYears = intYears

Proc_Exit:
  Exit Function

Proc_Err:
  MsgBox "Error: " & Err.Number & ". " & Err.description, , "modDateTime.AgeYears"
  Resume Proc_Exit
End Function

[Khalil Handal]

Hi to all

Thank all of you for their replies. It was very helpful.

Khalil