Calculate date of last record in related table

[gzamba]

Dear Brendan,

I have created a patient database with a main form called “patients” with all the patients’ basic information and a second (linked) form called “visits” where each record has the details of each patient’s individual visit.
The “patients” database has a field called “first visit” – which is automatically populated with “today’s date” when the first record is created.
I want to have another field called “last visit” within the “patients” database that will display the date of the last visit from the “visits” records.
So in plain programming words it would be something like “calculation = get date of latest record from “visits” table”.
How can I achieve this – if this is even possible?

Many thanks for your reply
George

[Brendan]

Hi George,

Sorry for the delayed response.

I do not have a function yet to grab any values from a child record and display it in a field on the parent record. Sorry.

[Bob Edelman]

I could really use the ability to display a value from a child record into a field in the parent record. I’m trying to build a basic inventory system for my wife’s hand-crafted jewelry business. I have forms for Parts, Purchase Transactions, and Vendors — where the Parts is a parent for Purchase Transactions and Purchase Transactions also relates to the Vendors form.

I want to display the vendor’s name into the form for Parts — but it doesn’t look like I can do that yet, can I?

– Bob

[Brendan]

Hi Bob,

On the Mac version you can display the inverse of a one-to-many Link to Form on the list of child records. So maybe that’ll get what you need? That would require the Vendors form to be linked directly to the Parts in a one-to-many relationship. That is, one Vendor has multiple Parts, but a Part can only belong to one Vendor. Or maybe you can put the Vendor name directly onto the Part?

[Bob Edelman]

Hi Brendan,

Thanks for the reply — but it’s possible for more than one vendor to sell the same “part”, but it IS a one-many relationship between a vendor and the related transactions. In short, in viewing the part I want all the related transactions to display along with the vendor name for the transaction — but when the transaction list appears, it doesn’t show the vendor name since that’s a link from the the transactions table to the vendor table (where I want it to display the Vendor Name.

I could place the Vendor name directly into the transaction table — and don’t bother having a separate vendors table (that shows additional information about the vendor such as address, phone number, contact name, etc.

Think of it as having a pick list that is then tied over to the information in a table — but I don’t think that’s possible.

– Bob

[Author]

Posts