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