9 Replies Latest reply on Jan 18, 2012 4:54 AM by Sorbsbuster

    Searching a summary field.

    Brucey

      Title

      Searching a summary field.

      Post

      I have a summary field, which counts the number of courses a person has attended.  Is there anyway I can perform a simple search on that summarised field?

      Eg course count >=3

        • 1. Re: Searching a summary field.
          philmodjunk

          Not directly, since a summary field does not refer to any one record, but could be the total of many records.

          You might define a calculation field that refers to the summary field and enter search criteria in it. Depending on how you summary field is set up, you may need to define the calcualtion using the getsummary function. (getsummary returns a sub total like you get in a sub summary layout part.)

          • 2. Re: Searching a summary field.
            Brucey

            Thanks for the reply.

            Not sure what the breakfield is in the GetSummary function and how it works.  Any chance you could explain please.

            Regards 

            Brucey

            • 3. Re: Searching a summary field.
              philmodjunk

              The "break" field is the field on which you sort your records to group them for subtotals. If you have a sub summary part in which to display a sub total, the "break" field is the same field as the layout part's "when sorted by" field.

              In your case, if you have a set of records listing classes attended with a class ID and a Student ID in each record, you might sort your records by student ID to group them by student. Then StudentID would be your break field.

              • 4. Re: Searching a summary field.
                Brucey

                Finally got the GetSummary function to work and placed it in to the body part. It displays the correct data, but when I search this field, I get the "No records match this find criteria" dialogue box.

                Any ideas?

                Thanks 

                Brucey

                 

                • 5. Re: Searching a summary field.
                  philmodjunk

                  Oh yea, the find will disrupt the sort on which this calculation is based.

                  Describe your database in more detail and what you want to do with this find. I think we need to use a different approach to get what you want.

                  • 6. Re: Searching a summary field.
                    Brucey

                    I've got 3 tables which contain some of the following fields.

                    Employee tbl (EmployeeID_pk, forename, surname, address etc)

                    Course tbl (courseID_pk, CourseTitle, Duration etc)

                    Courseattendance tbl (QualificationID_pk, EmployeeID_fk, CourseID_fk, DateAttendrd, Pass(boolean_yes/no) etc)

                    The CourseAttendance tbl is the join table. 

                    What I need to do is produce a summarised report of employees who have failed 3 or more courses.  Hence, the reason I am trying to get a solution to the above posts by searching a summary field. I thought it would be straight forward, but obviously not.

                    The report should show the the employee name with a list beneath each employee of the courses they have failed along with the dates they attended the courses.

                    It will also produce a sub total of the course failed. eg summary = count of pass

                    From that report, I need to produce a graph.

                    Hope this makes sense and you can help.

                    Regards 

                    Brucey

                    • 7. Re: Searching a summary field.
                      Sorbsbuster

                      You can have a new relationship from the Employee Table to the CourseAttendance table.  It would link by EmployeeID and (say) a Constant Field in the Employees table (set to be 'No') and the PassYesNo field in the CourseAttendance table.  Then you can create a calculation field in the employees table:

                      Count (Employees_CourseAttendanceByThatRelationship::CourseID )

                      That will let you see on the Employee's record how many they have failed, and you can search on it.  As it is unindexed it might be a tad slow.

                      You could make the 'Yes' field a global, with options Yes and No, and then change it to see how many they have passed, or failed.  You could add another global field to the relationship, 'CourseName', and then see how many times they have passed or failed a particular course.

                      You could also display some of that with a filtered portal from the Employees to the CourseAttendance table, with no new relationship.

                      • 8. Re: Searching a summary field.
                        Brucey

                        Not quite sure what you mean by a constant field in the Employees table (set to be "No").  Would this be a new field created as a calculation and set to = "No"?

                        • 9. Re: Searching a summary field.
                          Sorbsbuster

                          Exactly.  It is a common usage, a handy field to use in relationships to match to the child records that have a field set to 'No'.