Most recent data to specific fields

[LiveOak]

This first problem is that you are using multiple fields (FA1-15) where you should be using records in another file. You need a related file (Appointments) to store information on each appointment. If you did this, it would be EASY to display the most recent appointment by having a relatioship shorted by date and displaying the information from Appointments in a single row portal.

Using multiple fields or repeating fields to store this type of data makes it difficult to search or sort the data.

If your two files are Patients and Appointments, create a field in Patients:

Record Number (number, auto enter serial number, can't modify)

Create fields in Appointments:

PatientRecord (number)

FollowDate (date)

FollowTime (time)

FollowReason (text)

Relate the two files by matching Record Number <---> PatientRecord. Set this relationship to "allow creation of related records" and sort in reverse order by FollowDate.

Display the information from the Appointment file on a layout in the Patient file using a portal based upon the above relationship. Set the port to have a single row in which you place the above three fields.

I know this is a significant change to what you are doing now, but no sense getting your started in your database career closing wounds by cauterizing them with hot oil. Time for more modern medicine.

-bd

[digidiva]

the goal: to have the most recent (date of appt) (time of appt) (reason for visit) information appear from the (follup appt) layout to the main (patient info) layout.

Currently, the (follup appt) layout has fields (FA1..F15) follow-up date, (TA1...TA15) follow-up time, and (RV1..RV2) (reason for visit),

I would like the MOST RECENT of each event, based on the fields (FA1..F15) to be placed on the layout (patient info) automatically.

When the most recent appointment date changes, then the (patient info) layout fields will update automatically with the most recent information from the fields (FA1...F15), with the corresponding information from (TA1...TA15) and (RV1...RV2).

thank you for your input.

[digidiva]

I am looking into what you are suggesting. Here is one more piece of information that might have been missed: each patients Follow-up visit (fields FA1..FA15) needs to be stored for that patient. That is why I have chosen 15 fields. To allow for at least 15 followup visits (reality is about 3 visits per patient)...

[LiveOak]

That is why we have relational database systems. The information in Appointments is "related" to the information in Patients. That is to say every appointment is uniquely connected to a patient by the key in the PatientRecord field.

I can't stress enough that using separate fields is really bad design practice, listen to the doctor.

-bd

[digidiva]

Okay, I implemented what you suggested. Ignore previous post.

Question: I have 2 layouts in the Patients file. One layout lists the entire followup visit history for a patient, Followups, the other is the patientData layout, where the appointments are recorded and the most recent displayed. What I would now like to do is this:

on the patientData layout, where only the MOST RECENT followup appt appears, I would like to be able to do the entry into that field on the patientData layout of the followup appt, have that information goto the Appointments Database, and appear collectively in Patients layout:Followups:

I thank you for your solution and assistance.

[ October 01, 2001: Message edited by: digidiva ]