6 Replies Latest reply on Jul 28, 2010 11:50 AM by philmodjunk

    Grouped data report with data from different tables

    buck_5648798798

      Title

      Grouped data report with data from different tables

      Post

      I need to track student attendance for an after school program.

      The classes are once a week for 3 months.

      I have a table for students, one record per student.

      I have a table for the class with a portal to another table - class_lines. There is a line for each date the class meets. These are entered before the season.

      I need to create a report that shows each date from class_lines. Under each date are all student names from the students table. Next to each student name is a Yes/No checkbox for attendance. I can't figure this out...

      I've tried to create a report with grouped data organized by date but I can't get info from related tables to show up. I can get the dates from class_line or Student name from students depending on which table the report is based on.

      I don't know which table to put the attendance Yes/No checkbox in either.

      The relationships are as following:

      Classes ---> Class_lines <----> Students

      FMP 10 Mac 10.6.4

      Thanks!

        • 1. Re: Grouped data report with data from different tables
          philmodjunk

          You should have Classes----<Class_Lines>-----Students (one to many from classes to class lines and one to many for students to class lines.

          Then you can put your attendance check box in Class lines and base your report on the class lines table. You can put fields from both classes and Students as needed on this layout.

          The resulting report will likely be a summary report with subsummary parts to group entries by class and then by class date. If you are new to summary reports with subsummary parts, you might wish to study this tutorial:  Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Grouped data report with data from different tables
            buck_5648798798

            Hi. Thanks For your advice. I am (still) throughly confused...

            To simplify I've narrowed it down to 2 tables. Classes and Students.

            The relationship is Classes:student_id>-----Students:student_id.

            student_id is an auto enter S/N in the Students table. Classes:student_ID is a number field.

            The fields for Students are name and student_ID. The fields for classes are student_ID, Attended (y/n) and date.

            I tried Summary Reports with sub-summarys. I can't figure out how to make all of the student names show up under each date in classes.

            I followed the great tutorial you posted which has helped me in the past. I successfully created the database described.

            As a test I tried to modify the database to suit what I need. The equivalent would be to have a list of all customer names for each line item. I can't get it...

            The only way I've gotten close is to change Part Name to student name and make a new line item for each student name with the same invoice number. This doesn't seem right. 

            Obviously I have it wrong somewhere..

            Thanks for ANY help or advice!!

            • 3. Re: Grouped data report with data from different tables
              philmodjunk

              You really do need Classes---<Class_Lines>---Students.

              The Class_Lines table should have one record for each student for each class date.

              Then you can place an attendance field from Class_Lines as well as name and other student info from Students in the body of your report. You can place the class name from classes and the date from Class_Lines in a subsummary to form a subhead above each group of records that represents the attendance record for a specific date that the class met.

              The challenge is in correctly popluationg Class_Lines with the needed records. If all your classes meet on the same nearly same calendar of dates, you can automate the process of populating these tables in a batch operation. If each class has it's own meeting schedule, you can still automate this process, but you have to do it class by class... Check back here tomorrow and I may have a simplified demo file you can download and look at for this.

              • 4. Re: Grouped data report with data from different tables
                philmodjunk

                Ok, here's the demo: http://www.4shared.com/file/vgTCAbRr/ClassAttendanceRecord.html

                Please note that this is not by any means a finished product. It's just a proof of concept file so that you can see what I am describing. It would take a lot more design work to make this file into a useful database...

                • 5. Re: Grouped data report with data from different tables
                  buck_5648798798

                  Wow. Thanks a lot for taking the time to create that. 

                  Classes meet on the same day for a few months so I'll make a batch operation that lets the users pick a day and a date range and from that data the necessary lines will be created. I hoping to figure that out by myself but it's pretty intimidating...

                  Once again THANKS!!!!!!

                  • 6. Re: Grouped data report with data from different tables
                    philmodjunk

                    feel free to start a new thread and post questions if you hit a snag while trying to set that up.