[AccessD] Average ages

John Clark John.Clark at niagaracounty.com
Wed Jul 21 09:57:42 CDT 2004


Well, don't I feel the fool?! Thanks Gustav...as usual you have
enlightened me! I have never used the 'Totals' feature before. I had
basically thought it it would put the 'answer' at the end of the query
or something.

Thanks again...take care!

John W Clark

>>> gustav at cactus.dk 7/20/2004 2:24:24 PM >>>
Hi John

> This should be simple...I think I am just missing something simple
(and
> probably dumb).

Yes, but you need to Group By no field and only Select Avg(Age).
If you use your XAge, all Nulls count as Zero age which obstructs the
average completely.

/gustav


> I have a query that has the following fields:

> Client Name: UCase([txtLastName] & ", " & [txtFirstName])
> xAge: IIf(IsNull([datDOB]),0,Age([tblClient]![datDOB]))
> kSubContID
> lnkClient
> CareGiver (Criteria: True)
> DatEntry (Criteria: >=#04/01/04#)

> The SQL looks like this:

> SELECT UCase([txtLastName] & ", " & [txtFirstName]) AS [Client
Name],
> IIf(IsNull([datDOB]),0,Age([tblClient]![datDOB])) AS xAge,
> tblSubConts.kSubContID, tblSubConts.lnkClient,
tblSubConts.CareGiver,
> tblClient.datEntry
> FROM tblClient INNER JOIN tblSubConts ON tblClient.kClientID =
> tblSubConts.lnkClient
> WHERE (((tblSubConts.CareGiver)=True) AND
> ((tblClient.datEntry)>=#4/1/2004#))
> ORDER BY UCase([txtLastName] & ", " & [txtFirstName]),
> tblClient.datEntry;


> How do I get an average age for all of my clients in this query? I
> think I could muddle through this easier in a report or form, but I
just
> needed a number, and I tried using the 'Totals' section for the
query
> and choosing 'Avg' for the 'xAge' field. This does not seem to work
> though.

> John W. Clark
> Computer Programmer
> Niagara County
> Central Data Processing

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list