3 Replies Latest reply on Apr 8, 2013 6:52 AM by HeidiStewart

    count records if two fields match

    HeidiStewart

      Title

      count records if two fields match

      Post

           my database tracks students study status, full time new vs full time continuing.  a Masters student is considered full time new until they have successfully completed 6 semesters/terms.  a PhD student is considered full time new until they have successfully completed 9 semesters/terms.

           i'm trying to automate the change from new to continuing.  i created a table to track the semesters/terms completed.  1 record will be completed for each student for each semester/term they complete.  my table has a field for the student ID#, the academic year, the program (Masters or PhD) and which semester (Fall, Winter, Summer).

           i'm trying to find a way to have FM count the # of records for each student by the program.  so if my student currently has 5 records for 5 previously completed semesters of they're Masters degree, then when i add a new record that they have now completed the winter semester for their Masters degree it should show me that this student has now done 6 semesters**.  also, if in the fall the student starts their PhD program and i add a record to show they completed a semester of their PhD program it will show the student has now done 1 semester not 7 because it will not count the 6 they did towards their Masters.

           this does not take into account those who do not graduate from Masters but simply switch to PhD and are eligible to carry over their Masters semesters to count towards their PhD.... but i think that's probably way too complicated.

            

           **this would trigger the study status to change from full time new to full time continuing.

        • 1. Re: count records if two fields match
          philmodjunk

               To count related records, the count function can count them. Count (RelatedTable::Field ) will return a count of all related records where Field is not empty.

               An IF or Case function can check both the count and the student type(PHD or masters) to show the status that is appropriate.

               To not count records from a previous record, set up a relationship that excludes them or count a field that is empty for those records.

          • 2. Re: count records if two fields match
            HeidiStewart

                 ok this sounds good in theory, but i have no idea how to write a calculation using IF or CASE to make it count the number of records for each student ID for each program level.

                 i'm not sure what you mean by relationship that excludes other student ID's and empty fields.

            • 3. Re: count records if two fields match
              HeidiStewart

                   ok i created two calculations fields, one to count the semesters with MSc as the program and another to count the semsters with PhD as the program. 

                   I should be getting MSc = 6 and PhD = 3, but i'm not, i'm getting MSc = 9

                   Count MSc = Case ( Student Transcripts::Program = "MSc" ; Count (Student Transcripts::Semester) ; Student Transcripts::Program = "PhD" ; "0" )

                   Count PhD = Case ( Student Transcripts::Program = "PhD" ; Count (Student Transcripts::Semester) ; Student Transcripts::Program = "MSc" ; "0" )

                   My table has the field "Program" with a drop down of "MSc" or "PhD" and my field Semester is just a text box, i have Fall Winter or Summer written in this field.