ACCESS FEED: Re: [MS_AccessPros] Calculate average age

Sabtu, 26 Mei 2012

Re: [MS_AccessPros] Calculate average age

Hi Art,

John probably has something more elegant than this but here is what comes to mind:

SELECT tblApplicant.ApplicantID, tblApplicant.DOB, dAvg("GetAge([DOB], Date())","tblApplicant", "AppActiveFlag = -1") as AvgAge
FROM tblApplicant INNER JOIN tblApplication ON tblApplicant.ApplicantID = tblApplication.ApplicantID
WHERE (tblApplication.AppActiveFlag = -1);

I changed:
WHERE (((tblApplication.AppActiveFlag)=Yes));
to
WHERE (tblApplication.AppActiveFlag = -1)
and added the AvgAgecalculated field which uses a function call

To run, this function must be in a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~~
Function GetAge(pDOB As Date, optional pDate As Date = 0) As Integer
   GetAge = 0
   If Nz(pDOB, 0) = 0 Then Exit Function
   If pDate = 0 Then pDate = Date()

   GetAge = DateDiff("yyyy", pDOB, pDate) _
      + (pDate < DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))
End Function
'~~~~~~~~~~~~~~~~~~~~~~

function calls make processing slower, but they are so easy! I didn't test this -- but if you have to change it, it should be minor ... ;)

Warm Regards,
Crystal

Microsoft MVP, Access
remote training and programming

www.AccessMVP.com/strive4peace

www.YouTube.com/LearnAccessByCrystal
www.YouTube.com/LearnByCrystal

* (: have an awesome day :) *

________________________________
From: Art

I am tryhing to calculate the average age

I have this:

SELECT tblApplicant.ApplicantID, tblApplicant.DOB
FROM tblApplicant INNER JOIN tblApplication ON tblApplicant.ApplicantID = tblApplication.ApplicantID
WHERE (((tblApplication.AppActiveFlag)=Yes));

But how can I get the average age?

Thanks,

Art Lorenzini
Sioux Falls, SD

[Non-text portions of this message have been removed]