Does your example for John show three separate records or one record where the fields contain a list of 3 values?
I will assume that what you show represents 3 records. I will also assume that "unique number" identifies John, not the course he is expected to attend.
If those assumptions are correct, then you can define a summary field in this table, sCount as the count of any field that is never empty.
Add a sub summary layout part "when sorted by Unique Number".
Remove the Body layout part, put the Name and other fields you need in the sub summary layout part along with sCount to show the total No Shows.
Then perform a find for "No Show" in the Attendance status field and sort your records by Unique Number.
The result will be a list of names and the number of times that they were a no show. If an individual had perfect attendance, they will not appear in this list.
Your assumptions are correct, one person has 3 records (one record for each event) and the you are correct the "Unique Number" identifies John - not the course.
I get what you are proposing but I am hoping to:
a) have the total "no shows" appear as soon as the youth's status for that one event is entered,
b) have the total "no shows" always show when the kids name shows in any list in that table
b) not have to do a find and sort.
The people I am writing this for have 4 events in a weekend and four different people track/manage attendance for the youth in their event. What each person sees when trying to manage/track a youth for an event is the 8 kids who were signed up for their particular event and they then go to each kids record and mark their status as "Attended" or "No Show" ... When marking a "No Show" I'm hoping for a number to appear which reflects how many "No Shows" they have had.
Further, I need to total "No Shows" to appear each time the youth's name shows up on any list that has his name on it. This need makes the find and sort unusable. You sent me on to a related table some time ago but I only have about 100 hours into my attempts :)
Ok, then your original approach using a relationship to match to records is likely on the right track. A missing detail is how an individual gets assigned to a specific event. We need that detail inorder to distinguish the count of no shows for John for event 1 from the count of no shows for John for Event 2 unless you just want a count of all "no shows" for John regardless of the event.
I am thinking I need to distinguish event 1 from event two (eventually) I hope to have it find only the "No shows" over the past 90 days.
I'm not sure I grasp the intent of your question but, the youth gets assiged to an event by way of an event serial number. The process is where a particular youth (delinquent kids for your orientation) is found in a database by the youth's worker and the worker sees a list of possible events (a portal of events) on the right side of the layout and can assign a youth to any number of events by way of a portal on the left side of the layout.
Simply, I have three tables:
3) Event Youth List
The Event Youth List is a table that contains all youth signed up for all events. i can search by youth in this table and find how many events the youth has signed up for. I can search for events and find how many youth were signed up for any particular event. The Event Youth List table brings youth data in by the unique number and it brings event data in by way of the event serial number.
Am I helping?
It appears that you have these relationships:
Events-----<event Youth List>------Youth
Events::__pkEventID = Event Youth List::_fkEventID
Youth::__pkYouthID = Event Youth List::_fkYouthID
If this notation is confusing/unfamiliar, see this thread: Common Forum Relationship and Field Notations Explained
That would make event Youth List a "join" table between Events and Youth.
I would also assume that your example fields in your original post come from Event Youth List. Which would mean that for an event that is more than one day in duration, you are creating multiple records in Event Youth List with the same YouthID and EventID. If so, I recommend that you add another table so that you have one table as the "join" table that tracks event assignments and a second table that tracks attendance. A script can run through the Join Table records for a given event and generate the needed attendance records in the attendance table.
But using what you have right now, you can use the records in Event Youth List in relationships to get either their No Shows for each event or for all assigned events in the past 90 days. Here's the 90 example:
If you don't have it already, add sCount to Event Youth List as a summary field defined as the "count of" Unique Number (I called it _fkYouthID when I guessed your relationships.) Any field that is never empty will do as the field to be "counted".
Put a one row portal on your Youth layout to Event Youth List.
Give it this portal filter expression:
Event Youth List::Event Date > Get ( CurrentDate ) - 90 AND
Event Youth List::Attendance Status = "No Show"
Put sCount inside the row of this one row portal and it will show the number of no shows for a given Youth over the past 90 days. And a List View layout based on Youth can use such a one row portal to list the no shows for multiple Youth on the same page.