ACCESS FEED: RE: [MS_AccessPros] Calculate average age

Sabtu, 26 Mei 2012

RE: [MS_AccessPros] Calculate average age

Crystal-

That won't work because he potentially has multiple rows in tblApplication for
each applicant. See my earlier reply.

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Sunday, May 27, 2012 3:40 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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]

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