2 Replies Latest reply on Apr 2, 2009 5:23 PM by gorobei28

    Calculation difficulty



      Calculation difficulty



      Here is the scenario.  My database is to follow student training. I have a "Class Assigned" field, an "Attended" field which has a drop down list with the choices "Yes", "No".  I have another field called "Class Completed".


      I have a relationship between "Class Assigned" and "Attended".


      What I want to happen is this:


      When I enter a "Class Assigned" and choose "Yes" in the "Attended" field the information in the "Class Assigned" field will automatically go to "Class Completed" as well.  If I choose "No" then nothing happens to the "Class Completed" field. 


      These fields all are repeating because there will be multiple classes entered. 


      I can't seem to put togehter the right calculation to make this happen.


      I hope I explained this fully enough if not I will explain more. Any help would be appreciated.


      I hope I explained this fully enough


        • 1. Re: Calculation difficulty

          Using repeated fields is bad practice and best used for certain cases, like if you dealing RBG values which always come in a set of three numbers where each number ranges from 0 to 255. If its not something like that, then use a relationship instead


          You have students have many attendances

          You have classes have many attendances


          The Student table contains nameFirst, nameLast, address.

          The Class table contains nameOfClass, Teacher


          The attendances are record in a table called Attendance, and in this table there is a field that holds the foreignID for the Student ( an echo of the Students Unique ID ), there is also a field that holds the foreignID of the Class ( an echo of the Class Unique ID of the Class record that relates whichever attendance records that are related ). 


          So maybe you're in a layout called Student, that is based on the table occurrence Student that is based on the table Student ( where you defined your fields for Student ), then you have a portal that is based on Attendance ( where there is a relationship between Student the table occurrence an Attendance the table occurrence ). Note that the Attendance table occurrence also has a relationship to the Class table occurrence ( in your relationship graph ).


          So when you are in the Student layout, you create a new Attendance record ( remember to allow this within the relationship graph ), and then you're going to want to click in the the field in this new Attendance record where you can enter the foreign key for the Class you want to assign ( you'll want to utilize a drop down list to make this easy to work with -- otherwise you could literally just type in matching foreign record for a given classes unique ID number -- try this with the help of pen and paper before you progress further to test your relationship -- hint: two fields will be needed when you start to use the drop down value list approach, one which will show you the foreignID based on the unique ID in Class table ( and this foreignID must reside in the Attendance table, and must be the field that is viewed in layout mode ), and another field which shows you the name of the class but is resident to the Class table, i.e. name of class shows up only after a matching foreignID is put into the foreignID that is resident to the Attendance record ).  


          Once you've got that setup, now you can add another field ClassStatus, to display "Completed" or "In Progress", and the contents to be determined by Calculation, so it could be a Calculation field that returns text. But do you place this ClassStatus field, do you place it in Attendance, Class, or Student. Well a Class completed in the Class table, sounds like the class is done for the Semester, a Class complete in the Attendance sounds like it was finished for a given day, and Class complete in Student doesn't make sense at all, or rather, sounds like they graduated the school, hence all the classes. So maybe another table occurrence with a join table as well, for dealing with whether a class is finished... I'm chasing my tail with confusion at this point, and maybe you're just dealing with classes that are one time events? Even so, some classes might have one day events while other might be two or more events, so the Attendance table ( as a join table ) would still make sense. 


          Okay, maybe you don't  

          • 2. Re: Calculation difficulty

            Ok I will try this out and get back to you if I can't figure it out. Sry it took me so long to respond I have been out sick. This does sound like it may work. Thank you!


            And to clairify what I have going on. It is an apprenticeship class. We don't really work with semesters. There are classes given regularly on different subjects. So the same class (say Welding) may be given many times but with different apprentices attending.Usually the classes are only for 2-3 days.  I need to be able to pull up an apprentice's record and see what classes he has attended and completed.