AnsweredAssumed Answered

Finding an age/age range at the time of a diary event

Question asked by simonpsmith on Sep 21, 2016
Latest reply on Nov 9, 2016 by simonpsmith

I’ve been scratching my head exactly how to find and age of a ‘client’ at date that is determined by a certain event that’s been entered in a diary table.


To simplify, let’s say there are just two tables: CLIENTS and DIARY

CLIENTS contains, say, 4 fields: CLIENTid, CLIENTname, CLIENTdate_of_birth, CLIENTage_at_referral

DIARY contains, say, 3 fields: DIARYid, DIARYtype_of_contact, DIARYentry_date


In the DIARY layout, someone will enter a client ID number, select a type of contact from a value list, and enter the date that happened.


One of the DIARYtype_of_contact choices is “Referral” and I want to be able to, eventually, count the number of people in an age range that were referred during any period of time.


So, for each client, I need to calculate their CLIENTage_at_referral by looking up the DIARYentry_date for when the client’s DIARYtype_of_contact = “referral”, subtract their CLIENTdate_of_birth from this and return their age on that date. I can then do a ValueCount to get my totals (I hope).


Any help please?