Looks like you need two tables instead of the one that you describe. One table would record all the data specific to that patient such as the DOB, Ethnicity, gender and attending physician. A second table, linked to the first by PatientID, would record the details of each office visit.
With that relationship, no such calculations would be needed. You'd add the fields from the first table to the layout set up for the table of office visits and no data need be copied into each new record created for each new office visit by that patient.
That does make sense. Unfortunately, I absolutely have to work with one table only.
(This is a previously created database and I'm modifying parts of it)
Is there any way to do this in a single table?
My guess here is that you have some sort of script or something that creates a new visit for the patient.
You could create a self join of visits to visits and sort the link in descending order and then do some auto enter calculations for your fields and the calculated value would come from the new self join table accurance. This would populate your fields with the same data from the last visit.
I absolutely have to work with one table only.
If find that "absolutely" very difficult to believe. If you are modifying parts of it, one modification that you can easily make is to add that needed second table and use scripts and/or import records to set it up with the correct data.
PhilModJunk: My problem was that data has been already entered in this database and I was following a fixed set of instructions. also, I'm relatively new to FileMaker Pro. In any case, your solution was very neat and simple. I did exactly as you said and created a separate table eventually. Just finished with it, and it worked out beautifully! Thanks for your advice and help! You gave me great suggestions on a previous post too. This forum has never failed me :)
MarcMcCall: Thanks for your time Marc, but I did not need to try out your solution eventually.