9 Replies Latest reply on Nov 14, 2013 4:21 PM by philmodjunk

    Should be easy but I need some help

    appt

      Title

      Should be easy but I need some help

      Post

           As shown, I have a list of students and their results (I have not included their names in the screenshot, they are to the left of the list). Each result is stored in a linked table for that particular exam.

           What I need is to be able to calculate the number of Not achieved, Achieved, Merit and Excellence for the whole class. I can do a student count (there are 22) based on the number of records but I am confused as to how to count all the records in a specific table that correspond to the letters N, A, M & E

           I tried a summary by creating a calculation field Achieved = "A" then the summary count of Achieved but that just returns one value per record which is not what I need.

           Seems like it should be easy but I'm like I say I am confused.

           Cheers

      Screen_Shot_2013-11-04_at_5.14.45_pm.png

        • 1. Re: Should be easy but I need some help
          philmodjunk
               

                    I tried a summary by creating a calculation field Achieved = "A" then the summary count of Achieved but that just returns one value per record

               That sounds like a workable approach. Since it isn't, can you describe your tables/relationships in more detail and in exactly which table you defined this calculation field?

          • 2. Re: Should be easy but I need some help
            appt

                 Ok so I have a student table which is linked to by 4 other tables for each of the exam standards using a pk_studentID - fk_studentID setup. The final grade for each standard is calculated within the specific table and then displayed on the list view above (which is based on the student table). I had put the summary calculation within the student table but I can see that that won't work because it will summarise just one student. 

            • 3. Re: Should be easy but I need some help
              philmodjunk

                   Yes, but you can put the calculation and summary fields within the related table to get a summary value based on all related records--thus a total based on all records linked to a specific student.

                   Or do you need to combine data from the "4 other tables"?

              • 4. Re: Should be easy but I need some help
                appt

                     ummm.

                     Not sure.

                     Table ELEAS147 includes the field 'Grade' which stores either N, A, M or E

                     Also within the table I have added the following

                     NotAchieved                        Calculation          Unstored, ="N"
                     NotAchievedCount              Summary             = CountofNotAchieved
                     NotAchievedPercentage      Calculation          Unstored, = 100 / (Student::StudentCount / NotAchievedCount)

                     Achieved                        Calculation          Unstored, ="A"
                     AchievedCount              Summary             = CountofAchieved
                     AchievedPercentage      Calculation          Unstored, = 100 / (Student::StudentCount / AchievedCount)

                     I created a layout based on that table but the figures for both end up the same in the layout.

                     There are 22 students and it shows 22 for both the not achieved and the achieved count

                      

                • 5. Re: Should be easy but I need some help
                  philmodjunk

                       I thought that you were evaluating this from the context of the student table?

                       A summary field in ELEAS147 will produce a total specific to a given student if referenced from a layout based on students. It will produce a total determined by the current found set if referenced on a layout based on ELEAS147.

                       Either there are typos in your last post or your NotAchieved and Achieved calculation fields are not set up correctly.

                       NotAchived should be defined as Grade = "N"

                       Achieved should be defined as Grade = "A"

                       Which is not what I see in your last post.

                  • 6. Re: Should be easy but I need some help
                    appt

                         Hi, Phil

                         been working on other stuff.

                         I have created the fields in the student table now as follows

                         students::achieved147 = ELEAS147::FinalGrade = "A" (unstored) I have tried both text and number as the output format

                         but I am struggling to then get a calculation of how many "A"s there are in that standard. There are 12 students with an "A" but the figure I get in the footer is either 22 or 1 depending on what I do to create the calculation.

                         I have tried 

                         students::achievedcount = count (achieved147) as a calculation

                         and

                         students::achievedcount = summaryof achieved147 as a summary

                         I'm obviously missing something obvious.

                    • 7. Re: Should be easy but I need some help
                      philmodjunk

                           Let's back up a second. What I was trying to determine in my last post was: a) what data is in the ELEAS147 table b) how is the data structured in that table, c) what kind of count from that table do you want? and from the context of what layout/table do you need to see the count(s)?

                           Do you want the count of how many "A" grades are in the entire ELEAS147 table?

                           Do you want the count of how many students in the student table have at least one A grade in the ELEAS147 table?

                           Is there just one record in ELEAS147 for each student or multiple records for each student?
                            

                      • 8. Re: Should be easy but I need some help
                        appt

                             Ok,

                             I will try to explain.

                             The Student table simply stores stuff such as their name, email address etc. It has a primary key defined as a serial number for each student.

                             The ELEAS147 table stores information about Electronics Achievement Standard 1.47. It has a primary key for each record but is linked to the student table via the following

                             Students::pk_studentID = ELEAS147::fk_studentID

                             The ELEAS147 table then has a bunch of tick boxes each with their own field. As students complete tasks the boxes are ticked and if all boxes are ticked then their ELEAS147::FinalGrade will show either "A", "M" or "E". (Achieved, Merit, Excellence)

                             If any boxes are not ticked then the grade will be "N" (Not Achieved).

                             There are 3 other standards each set up the same way and each standard is presented on it's own tab. The system works fine, you can look at each student's page, flick through the tabs etc and see what they have achieved.

                             So, currently I have 22 students and 22 records in the Student table each with a matching record in the various Standards tables.

                             I have a class list as shown in the previous screen shot that is based on the student table and has fields that show the grades for each of the standards.

                             What I would like at the bottom of the page in a footer is a count of how many students have achieved each grade of a standard. 

                             So for example I would see something like

                             Not Achieved    3
                             Achieved          12
                             Merit                 5
                             Excellence        3

                             Ultimately I will need to also calculate those figures as percentages, but first I need the figures to actually show properly.

                              

                        • 9. Re: Should be easy but I need some help
                          philmodjunk

                               The key detail is that there is only one record in the related table for each record in the students table. Your counts at the bottom of the page are sometimes referred to as a "summary recap" and either ExecuteSQL or a portal may be used to show those counts.

                               See this thread to learn more about how to do it with ExecuteSQL: FMP 12 Tip: Summary Recaps (Portal Subtotals)