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?