The Max() function is what you need. Let's make sure you're setting it up correctly.
In your "LastAttended" field, which is in your Client table, set it up to be a calculation field.
Make sure the result is a date.
set the calculation to be:
Max ( Attendance:: Date) ###Choose the Attendance:: Date field from the upper left pane ###
Put the LastAttended field on your client layout.
Find a client that has attended multiple times and look at thier record in the client layout...is it correct?
Look at a couple other clients too.
If they are not correct, please describe what you see for the Max() result, and what dates the client attended.
Note: extra spaces added after colons to avoid smileys
Thank you, Ninja, so much. I am now beating my head against the wall. In shame. It works like a charm. The only thing I was doing wrong was creating the LastAttendance field in the Attendance table, rather than in the Client table. I really appreciate your help.
Another possible way, which I believe would be faster, would be to perform a find from the Client table. It would be:
Enter Find Mode [ uncheck pause ]
Set Field [ Attendance::date ; " ≥ " & Date ( Month ( Get ( CurrentDate ) ) ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) - 1 ) ]
Set Error Capture [ On ]
Perform Find [ ]
Show Omitted Only
... add the proper error trapping of course. This will show you all clients who have attended within one year from today. Then when you Show Omitted, you will have a found set of clients who haven't attended within one year from today. You don't need to have the attendance field on the client layout to perform the search if you use a script and DO use your Attendance::Date field for searching instead of a calculation field in Clients because the attendance date will index in the attendance table. :smileyhappy:
Thank you LaRetta, I'll try this too. I'm just so happy to have even one solution that I can use tomorrow! To have two is almost too much to have hoped for.