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?
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.
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"?
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
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.
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
students::achievedcount = summaryof achieved147 as a summary
I'm obviously missing something obvious.
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?
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
Ultimately I will need to also calculate those figures as percentages, but first I need the figures to actually show properly.
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)