7 Replies Latest reply on Nov 17, 2015 5:14 PM by Chant62

    Displaying records

    Chant62

      Hi! I am currently re-writing my database that I use to run my karate club. It was never constructed corectly so I'm now going to redo it from the ground up. My first question follows:

      I have a student information table and a student record table. They are connected by Serial and student _FK, g.month = month and g.year = year.  On a layout I have each student in list format and the relevant information such as name, phone number, dues paid or not, and most importantly the ability to create records as to whether they attend class (P) or not (X). When I click on a Mon., Wed., or Fri. it creates a record in the student records table for each day/date that they are there and when they are not. This shows up as either P or X.  Class is only 3 days per week with one class on that day. What I want to do is create a layout that shows the student's attendance monthly but for 4 months that I select. It will always be a 4 month period but could consist of any 4 consecutive months. These month fields will show a number of attends (P) only such as 11 not the abscences (X). Then I want to add the available classes up for that 4 month selection (both attend and abscent) to get available classes so I can calculate an attendance percentages. This would be in a list format showing all current students and their attendance. I can accomplish this with the current month but not previous months although the data is there. I have a dummy file that might go a long way in explaining this a little better if anyone wants to take a gander at it.

      Thanks,

      Chant

        • 1. Re: Displaying records
          dsvail

          yes,  show us your example file so your issue can be better understood

          • 2. Re: Displaying records
            Chant62

            Dsvail,

            Found it!

            Thanks,

            Chant

            • 3. Re: Displaying records
              Chant62

              Dsvail,

              When the file opens it will show an attendance layout for Oct. 2015. By selecting the drop down Month and Year in the top right hand corner and clicking on the Setup button above it you can change it to whatever timeframe you need. The Sample Data I used was entered in Jan., Feb., Mar., and Apr., 2015. I hope this helps.

              I'm thinking I need a script and variable to loop through the records and populate the months on the report but I have no clue how to do this.

              Thanks.

              Chant

              • 4. Re: Displaying records
                bigtom

                Do you want this to run by months of the previous 120 days? If you select the current month (mid Nov 2015) will you want to see only data from Aug 1st or will you want to see some of the data from half of July?

                 

                If you can do this with one month getting a few more months in there is not that tough.

                • 5. Re: Displaying records
                  Chant62

                  BigTom,

                  You are correct. I would want to see data from Aug.1, 2015 thru the current month (Nov.) or half month if that applies. What I have is this situation - Students are tested every 4 months. They must have at least a 75% attendance rate before the test date. I want to input (or select) the months that are relevant and have those fields populate.

                  Is this a bit clearer? I know I have a long way to go but I have to start somewhere.

                  Sorry to edit my reply but I have to hit the hay - I gotta be up at 4:00 am tomorrow for work. I'll check back tomorrow.

                   

                  Thanks,

                  Chant

                  • 6. Re: Displaying records
                    dsvail

                    Chant,  my suggestion, since you are taking the opportunity to rebuild this from the ground up is to get solid the table structure and how they are related.  I would explore creating/ adding a table for Class and a Join Table “joining” Students to Classes.

                     

                       

                     

                     

                    Alex Mitchell

                    Digital Systems

                    (970) 904-6600

                    • 7. Re: Displaying records
                      Chant62

                      Alex,

                      Sorry I haven't gotten back sooner - work schedule can beat me up at times. Thanks for the reply. I can create another table and join it to students. I hadn't seen a need for this due to the nature of my club. I only have and will only ever have one class regardless of the days it's held. I have a few folks that help out but I'm the only certified instructor. We have had this schedule since 1986 (Mon., Wed., and Fri. class 1 1/2 hours long) and it's as much as our small town can support. The Attendance Report has to be structured so I can print it out and post it on the dojo board for students to see shortly before test time, hence the desire to have it in List view. Any more information I can give please just ask. I need your help.

                      Thanks,

                      Chant