1 2 Previous Next 18 Replies Latest reply on Apr 5, 2014 8:13 AM by PrisonProfessor

    Suggestions on best approach attendance sheet



      Suggestions on best approach attendance sheet


           Trudging along in overall dB development. The next big tasks is Keeping attendance weekly and than a history

           I envision something similar to what I'm currently doing in a spreadsheet. 

           Header portion will have Class start and end dates, class name & code, class text, facility location and sponsor name. This data is already in an existing table HistorySOCPMClasses with a few assertional fields related to student counts for begin & end number of students and counts for students; added, removed, transferred, paroled, dropped (removed is not a sum of last 3 but an independent count.

           A sub heading would be assigned to each class session; date, session number & topic

           Summary would have a sum of all the above counts for each weekly class and a sum of all those counts

           The actual body would be a record for each student enrolled and his status for each class which can be anywhere from 4 to 20 occurrences the fields would be student Id, Last Name First Name (this info is in a table called ProfileStudents.

           For each student for each week of class would be a status field of P, X, T, R, D in short it be a matrix 30 x 12 if I had 20 students in a class that lasted 12 weeks. 


           It is this matrix portion that is my biggest challenge. I’m thinking a portal for each student and his record of attendance but how to best incorporate the matrix into  a portal that already has a column in place for al weeks. All I need to do is fill in the students status for the week.

           I hope this is clear appreciate any suggestions the more detail the better as I’m not no where near a skilled FM Pro developer

        • 1. Re: Suggestions on best approach attendance sheet

                    in short it be a matrix 30 x 12 if I had 20 students in a class that lasted 12 weeks.

               Wouldn't that be 20 x 12? For what would you use the other 10 cells in this matrix?

               Am I correct that you are tracking attendance for multiple subjects that each can be scheduled to meet for different dates/times?

               So a class in Job Search methods might meet for 3 weeks i the morning in January and also in an afternoon time? And then a month later you have other sessions for that class?

               Will students be enrolled in specific class sessions or are they allowed to attend session 1 in  one time slot and session two in a different time where the same class is offered? (I would think not, but the devil is in the details...)

               The basic approach can be set up to start as a single column of fields for recording attendance for a single class session. Once you have that working we can move toward a "grid" display using portals that enables you to record attendance with each session in a different column.

          • 2. Re: Suggestions on best approach attendance sheet

                 Typo on my end yes for 20 students 12 weeks it would be 20 x 12 though size could vary depending on class length and number students

                 Yes you are correct in my tracking  attendance for multiple subjects that each can be scheduled to meet for different dates/times.

            For more of those 'devilish details' type stuff I currently have 2 major course subjects Christian Living and Discipleship.

            CL currently has 1 curriculum fatherhood and1 text. There are plans to expand CL subject to include a curriculum on marriage and eventually  curriculums in re-entry, employment, community, household management, etc.

            Discipleship already has multiple curriculums; Bible study, theology, doctrine, apologetics, etc

            Currently there are 5 classes per week in two prison facilities.

            On Wednesday in one prison there is a dad's class in the am and a discipleship class in pm.

            On Friday in another prison there is a dad's class in the am and a discipleship class in the afternoon and again in evening.

            It is possible some students can be in both the dads and discipleship, same prison though angel 

            I think I understand your other assumption to be correct in that there could be a class job search methods (dads) start in January then end in March and start again in March using same text. It is quite possible there may be more than one text in the future.

            Discipleship classes currently have one text per curriculum until whole cycle of curriculums are completed and then are cycled through again.

            Maybe more info than needed but figure better to much than not enough.

            Are my initial thoughts on how you want me to start correct; A table with 4 fields ,Student ID, Last Name, First Name, 1st Class Attendance




            • 3. Re: Suggestions on best approach attendance sheet

                   This post is going to focus on "theory" more than "practice". I'm trying to lay the necessary conceptual ground work so that the "nuts and bolts" will make sense to you when we get to that point.

                   To rephrase one of my unanswered questions: "Do the inmates have to formally enroll in the course or might they just "drop in"?"

                   I will assume that they have to sign up for each course and that this provides you with an initial roster of students for each course.

                   What I'm reading here is the description of a many to many relationship.

                   The first such set of relationships:


                   Any given student can enroll in many different classes--either all at the same time or as time passes. Any given ClassSession will have multiple students. A course, such as Discipleship can be offered more than once--either at different times of day or on different dates. So there will be one record for Discipleship in Courses, but each date/time that you offer the course, you'll create a new record for it in ClassSession.

                   Here are the match fields for these relationships

                   Students::__pkStudentID = Enrollment::_fkStudentID
                   ClassSession::__pkSessionID = Enrollment::_fkSessionID
                   Courses::__pkCourseID = ClassSession::_fkCourseID

                   Now that's just the foundation of what you need to track who has attended a given classSession on a particular date. You still need an attendance table where you have one record for each student for each day of the scheduled class session. (Yes, we can get this into a grid, but first things first.)


                   Enrollment::__pkEnrollmentID = Attendance::_fkEnrollmentID

                   In addition to the _Fk field, you'll want a field for the Date and perhaps the time or a "period"--there are different options that may or may not make this field necessary and a field for recording the "P, X, T, R, D" status for each date of the class.

                   So once you have your tables/relationships in place, you can enroll inmates in specific sessions or "class offerings" to get your initial roster of students for each. The next step is to create a set of records in attendance, one for each inmate on your roster for a given class session for a given date. This can be scripted.This would make it possible to set up a list view layout based on attendance where you could include a name field from Students. This would produce the first two columns, one for the names and one for the status---not the final result that you want, but a step in that direction. Once you have that much working we can talk about how to get your attendance matrix to appear from these tables and records.

                   See how much of that you can get working and I'll then supply more detail at the points where you "get stuck" making this happen.

              • 4. Re: Suggestions on best approach attendance sheet

                     Really appreciate all your help. I will work on this today.

                     Your theories sound correct in relation to the objectives I have envisioned. Yes, students must enroll but because of a long wait list it is very likely that if someone leaves the class for whatever reason and the class is relatively new a student can be added from the wait list.

                     It probably is not surprising to you that prison admin. Students are issued passes weekly or monthly, some studetns will not get a pass every week and of course some just come when they feel like it. Admin can remove students several reasons which I'm seldom given notice and since the chaplain left at one facility and there hasn't been one at the other facility for a long, long time I'm clueless as to what is the wait list has in the way of names and numbers. I say all that so as you can get an idea of the challenge to administering it all on my end trying to plan for all possibilities.

                     I Have a few other smaller tasks I want to close before tackling this. One already closed was my updating the lost data to about 95% accuracy. I'm going to have to change my method of backup by reviewing data more closely prior to overwriting. I plan to make more use of the multiple sequential number copy method FMP utilizes. and maintain a log of what changes I make in each copy save.

                     As in all things learn as you go.

                     Thanks again 

                • 5. Re: Suggestions on best approach attendance sheet

                       I had hoped to get this done today but looking less likely as family obligations are upon me, plus a new printer I ordered arrived and I need to configure it into home network.

                       What I'm thinking I'll do is paste or upload image of spreadsheet with tables and their respective fields for the above suggestions.


                       One of my many shortcomings is sometimes putting a field into a table that is redundant  of a field in another table. Thus some tables I've already created I'm thinking not are fields not needed in the table but just a lik to include it in a layout and I want to clean that up too before passing on to you what I have

                       Thanks and not sure where you're located but stay warm. I'm 70 miles west of Chicago and were expecting high winds blowing snow and all that fun stuff on top of bitter cold

                  • 6. Re: Suggestions on best approach attendance sheet

                         I'm back; Simply a lot of distractions past few days. As I mentioned I'm challenged in when a table (A) requires a field without it being redundant of a field in another table (B) versus simply using table (B) field in a layout 

                         I've attempted to list the table as I see them defined from your previous post. I've also attempted through color codes to show the key links and table (A) to table (B) & the fields from table (A) that need a field in table (B). I hope that all makes sense. I'm certain I'm missing key points from your post but I figure from my misunderstandings it be easier for me to get them correct once you can review and attempt to explain and for me to grasp on what tables will need what fields - Thanks

                    • 7. Re: Suggestions on best approach attendance sheet

                           You may find the discussion in this thread helpful as we were discussing school attendance and record keeping there before this thread began: Horizontal portal

                           You do not need the name fields in the Enrollment table.

                           What is the difference between cp_CourseCode, cs_ClassCode and _pkCourseProfile? At first glance, they look like three fields for the same data. If cp_CourseCode is from an external source--such as something supplied with your course curriculum and cs_ClassCode holds the same data, then CS_ClassCode is not needed. cs_Classcode is clearly not needed in the Enrollment table.

                           Likewise cp_Coursename is not needed in ClassSession.

                           The basic rule of thumb is that the exact data should never be stored in more than two tables unless it is used as a match field in a relationship.

                           The apparent exception to that rule is when fields are set to copy (looked up value and auto-enter calculations) data from one related table to another. But such fields should only be set up when you need to capture the "current value" of a field and you don't want the looked up value to change should the original be changed. But that difference in behavior is what keeps the data from being truly "the same data" as the field in one table stores the "current up to the minute value" and the field in the other table stores the "value that was current at a specific point in time".

                           There are indeed true exceptions to the above rule--usually to improve system performance by "denormalizing" the data so that a given layout doesn't have to pull data from a very large number of related records, but you wouldn't normally set that up during the initial design stages of your database development process. Often, you'd only add that capability when performance issues are encountered during testing and/or use of the system.

                      • 8. Re: Suggestions on best approach attendance sheet

                             cp_CourseCode is more of a group and perhaps be better defined as cp_CourseGroup. It is 4 character code. Currently valid values are; D101, RNFH, LIFE, MDAD

                             It is part of the cs_ClassCode.

                             Class Code is a concatenation of codes and counters something you helped me with previously in regards to the counters.

                             _pk_ClassProfle + cd_CourseCode + cp_CourseCount + fp_FacilityCode + cp_SOCPMFacilityCount + cp_FacilityCount

                             _pk_CourseProfile is the key field for the table Course Profiles

                             By match field do you mean the keys that define/connect the table relationships?

                             I know an individual who designs dB but does not know FMP. I think what might be helpful for both of us for me to get past theory and ground work is put this request of attendance sheet on hold until I meet him to assist me in table definitions and once I have table definitions properly defined get back to you on attendance sheet. Saves you the frustration of trying to explain to my shortcomings table/relationships

                             Would you agree or are you willing to go for the whole gambit. I'm thinking with all the posts you respond to and your own personal consulting it be beneficial to you time wise.


                        • 9. Re: Suggestions on best approach attendance sheet

                               I think you mean:

                               _pk_ClassProfle & cd_CourseCode & cp_CourseCount & fp_FacilityCode & cp_SOCPMFacilityCount & cp_FacilityCount

                               The + doesn't look like the correct operator here. But this definitely is data that should be in one and only one table, not copies across multiple tables. (When you put the same data in more than one table, you run into problems should you ever need to update the data--such as to correct an error.)

                               By match field do you mean the keys that define/connect the table relationships?


                               It's up to you I can see advantages to either option. What I saw in the lists of fields is very close to what I was recommending. There were just a few fields where they were placed in more than one table and did not need to be so placed. Not only is this easy to correct, if you left them in the table and didn't use them, it would not be a major deal--you just end up with a few more fields in your table than you are actually using.

                          • 10. Re: Suggestions on best approach attendance sheet

                                 It may take a while for me to connect with my dB guy so for now will continue as we are as I like to get this task done and if get lost bring him in.

                                 I'd prefer to clean up the tables so as not to confuse me down the road. One I see is start and end dates in 'HistoryInmateClasses' & 'Class Profiles'

                                 My schedule is getting busy so it may be a few days to conplete but if I run into issues (likely) in cleaning up I'll seek you out





                            • 11. Re: Suggestions on best approach attendance sheet

                                   I'm back, did you miss me wink My schedule just got busier and busier.

                                   Anyway I've created the  tables as the screen shot indicates. The fields I see as not pertinent to what I'm attempting to do I did a strikethrough. I've color coded the links and noted examples for fields that might be ambiguous by name only.

                                   If this all makes sense and is correct the next step would be the matrix, correct.

                                   As always thanks

                              • 12. Re: Suggestions on best approach attendance sheet

                                     A better way to show this is with a screen shot of Manage | Database | Relationships showing the actual links between occurrences of each of these tables.

                                • 14. Re: Suggestions on best approach attendance sheet

                                       I think you'll get better results if you make two occurrences of Student and one occurrence of Enrollment. Link one occurrence of Student to Attendance and a different occurrence of Student to Enrollment.

                                       And your screen shot does not show how Courses links in to this system.

                                       You may also find this article on Anchor Buoy--a way to organize table occurrences into useful groups for better management of your database design helpful: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                                  1 2 Previous Next