ACCESS FEED: Re: [MS_AccessPros] Calculate average age

Minggu, 27 Mei 2012

Re: [MS_AccessPros] Calculate average age

Yep!!! I duhhed it. THat works great.... ? What can I say, its Sunday and I am working. :)

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, May 27, 2012 8:40 AM
Subject: RE: [MS_AccessPros] Calculate average age

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]

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