Art-
From your previous question, you clearly have multiple rows in tblApplication
for each applicant, so the average will be wrong. Try this:
SELECT tblApplicant.ApplicantID, Avg(DateDiff("yyyy", [Bdate], Now())+ Int(
Format(now(), "mmdd") < Format( [Bdate], "mmdd") )) As AvgAge
FROM tblApplicant
WHERE tblApplicant.ApplicantID IN
(SELECT ApplicantID from tblApplication WHERE tblApplication.AppActiveFlag =
True)
GROUP BY tblApplicant.ApplicantID
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 Art
Sent: Saturday, May 26, 2012 11:17 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Calculate average age
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]
Tidak ada komentar:
Posting Komentar