[AccessD] Average ages

Gustav Brock gustav at cactus.dk
Tue Jul 20 13:24:24 CDT 2004


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




More information about the AccessD mailing list