Art-
Duh. Shouldn't include ApplicantID.
SELECT Avg(DateDiff("yyyy", [DOB], Now())+ Int(
Format(now(), "mmdd") < Format( [DOB], "mmdd") )) As AvgAge
FROM tblApplicant
WHERE tblApplicant.ApplicantID IN
(SELECT ApplicantID from tblApplication WHERE tblApplication.AppActiveFlag =
True);
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 Lorenzini
Sent: Sunday, May 27, 2012 2:32 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Calculate average age
OK. I tried this:
SELECT tblApplicant.ApplicantID, Avg(DateDiff("yyyy", [DOB], Now())+ Int(
Format(now(), "mmdd") < Format( [DOB], "mmdd") )) As AvgAge
FROM tblApplicant
WHERE tblApplicant.ApplicantID IN
(SELECT ApplicantID from tblApplication WHERE tblApplication.AppActiveFlag =
True)
Group By ApplicantID
But it returns a list of applicantID and ages. I need the over average age...
With warm regards,
Arthur Lorenzini
Sioux Falls, South Dakota
________________________________
From: John Viescas <JohnV@msn.com <mailto:JohnV%40msn.com> >
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Sunday, May 27, 2012 12:15 AM
Subject: RE: [MS_AccessPros] Calculate average age
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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Art
Sent: Saturday, May 26, 2012 11:17 PM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.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]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar