calculate age from 2 separate tables?

[dlazenby]

Searched the forums, help file and tinkered around with this, and I don't know how to do it.
Table 1: Patient (Pt_t); Fields include:
Pt_Id (numeric, primary key), pt name, DOB (date field), SS#, etc.
Table 2 Patient visit (pt_vst_t); for any one patient there will be multiple visits to the office;
Fields include:
Pt_vst_id, Pt_Id (numeric, foreign key), Pt_Vst_Tm (time field instead of date field b/c patient may be seen more than one time in a day; e.g., seen in office in AM, comes back in PM b/c problem)
The 2 tables are joined in a set (pt_vst_s) by the Pt_Id field with the Pt_t table being the parent table and the Pt_vst_t table being the child table.
I wanted to add an age field (age at time of visit) to the pt_vst_t table but can't seem to do it.
I added a field to the pt_vst_t called Pt_age making it a numeric field with width of 3; I then went to field rules -> field types, chose calculated, -> expression builder and entered age(); the field that it needs is the DOB from the Pt_t table which is not a choice;
How do I get this to work? Thanks

[Stan Mathews]

Re: calculate age from 2 separate tables?

When using a set fields from both tables are available for forms, reports, etc. Not sure why you feel the need to store that info in a table. You can use a lookup() to the child field value in your table calculated field if you must store it.

[dlazenby]

Re: calculate age from 2 separate tables?

Stan,
Thanks for your reply.
I added a calculated field to the form for the age. In the expression builder I entered age(dob,Pt_Vst_T->Pt_Vst_Tm) but it won't work with a time field. Able to get that to work when the field is set up as date type but not for time type. I guess that I could have a date field and a short time field for the office encounter "time-stamp" and then have the expression as
age(dob,pt_visit_dt) but that would add an extra data entry item (short time) for the user and not be as "clean".
Again, the patient occsasionally might be seen twice in one day, hence a time type field instead of just a date.
Any different suggestions? Thanks.

[Stan Mathews]

Re: calculate age from 2 separate tables?

In the expression builder I entered age(dob,Pt_Vst_T->Pt_Vst_Tm) but it won't work with a time field.

There are all sorts of date-time-shorttime conversion functions available.Perhaps you can find one that will let you derive the date from the visit time.

[dlazenby]

Re: calculate age from 2 separate tables?

Stan,
Thanks for your help. I'll try some of those conversions.