5 Replies Latest reply on Oct 5, 2012 10:36 AM by philmodjunk

    I tried, I failed!  I need to COUNT how many times a person failed to attend.



      I tried, I failed!  I need to COUNT how many times a person failed to attend.


           This is a post where I am admitting failure - a humble post. I was previously advised that a relationship could get my COUNT for me but I failed to get it to work.

           I have a "Youth" Table where one person can be scheduled for multiple and therefore any one youth can have several records in the "Youth" table, (one Youth record for each event he was scheduled for) and the youth could "Attend" or "No Show" for each of those events. Of all the other youth who are scheduled for events, I need to count how many times any one person did not attend. Simply stated, the Youth Table has the following fields:

      Name: John

      Unique Number: 19

      Serial Number: 19-1, 19-2, 19-3 ( a unique serial number for each time John is scheduled)

      Event Date: 8/1/2012, 9/1/2012, 10/1/2012 (Each event John was scheduled for)

      Attendance status: Attended, No Show, No Show (John's attendance status for each event)

      Count: (Reason for Question - Doesn't work) John had 2 no shows (per Attendance status)

           In my failed attempt I created a second table (Table 2) related by Unique Number which will count (by way of a summary field) the "No Show's" if I manually create a new record within it, and it will report the total back to (Table 1) but I can't get it to create a new record in Table 2 for each event that has a new status because Table 2 always relates to the first Unique Number in Table 1.

           I'm thinking this should be much easier than I'm making it but I have three weeks into it and am desperate. Any help would be much appreciated!!

        • 1. Re: I tried, I failed!  I need to COUNT how many times a person failed to attend.

               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.

          • 2. Re: I tried, I failed!  I need to COUNT how many times a person failed to attend.

                 Thanks Phil!!!

                 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 :)

            • 3. Re: I tried, I failed!  I need to COUNT how many times a person failed to attend.

                   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.

              • 4. Re: I tried, I failed!  I need to COUNT how many times a person failed to attend.

                     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:

                     1) Events

                     2) Youth

                     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?

                • 5. Re: I tried, I failed!  I need to COUNT how many times a person failed to attend.

                       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.