Using an Update Query to transfer Grades

[Davidwazza]

Hi Everyone and Anyone!

My problem: I need to make an update query to place scores from Course Reports, to Individual Student Tables.

These are my tables...

   tblStudent
      Stores all of the Student details.
         strStudentID
         strSurname
         ...etc

   tblStudentAcademic
      Stores all of Students' Subject Scores (compulsory).
         strStudentID
         intMath
            Percentage in Subject Maths
         strMath
            Grade -> D, C, P, F
         dtmMath
            Date Completed
         ...etc

   tblCourse
      Teachers use this to fill in details of their course.
         intCourseID
         strSubject
            Choose from list, eg. Math
         dtmDate
            Date completed course.
         ...etc

   tblCourseGrade
      Subform which goes with tblCourse.
          intCourseID
          strStudentID
          intPercentage
             Percentage attained for course.
          strGrade
             Thus grade received eg. D, C, P, F.

Phew! Sorry people! Just best to give everyone all the information. Anywayz, you may have guessed, I want to make an Update Query to transfer the grades from the Course, into the Students Grades.

I tried, in qupdStudentAcademic, to update tblStudentAcademic, to set

   Field: intPercentage
   Table: tblCourseGrade
   Update To: "int" & "[Tables]![tblCourse]![strSubject]"

but it said it was a conversion failure.

Obviously, I did a similar approach to strGrade, and dtmDate from tblCourseGrade and tblCourse respectively.

Anyone got any ideas? Greatly Appreciated!

 

[jrbarnett]

Hi

Your "tblStudentAcademic" is not normalised. I would have a table with min/max percentages and a grade, that way you needn't store the grade and the percentage.
Also remove the subject specific items, so there is

strStudentID
strSubject
intPercentage
dtmDate

You then don't need the tblCourseGrade table because all the data are held in the one.

Secondly, don't have individual tables for individual students because it will further denormalise and clutter your database. Instead, keep them all in one table and use queries to bring out student specific information.

This will make it a lot easier to bring together individual student data as it only comes from two rather than 4 tables.

John

 

[Davidwazza]

Hi there, thanks for responding.

In regards to your issues
- I originally had what you were talking about firstly - percentages are entered, and the grades are calculated based on minimum and maximum cutoff grades from another table, exactly what you were talking about. Problem was that there were also other things that affected the grade. You see, if people fail the exam, they have the opportunity to resit the exam, and gain a Supp (S). Hence, this was not accounted on a auto calc grade system. Though, to accomodate this, I firstly did have a Yes/No box with question SupPass? It would look at this first, and if it is No, go on to auto calc.
Then there were other possibilities, such as W for withdrewel, X for deferred assessment etc. Thats when I thought it would be just as easy, and more understandable to just get the user to do it themselves. Thanks for poitning that out to me anyway

- Also, again I did trial of having a table which you go just down and select the subject, but I didnt find this as good as having a table with all compulsory subjects spelt across. For one, is there a way to find out if the student has done a subject - like is there a code for finding out if a line does not exist.
eg. Using your method, if someone has not done Maths for instance, there would be no such row. For mine, at least you could run a query that tests if strMath IsNull etc.
So is there a way to find this out? Because it would really come in handy for other things too!

- Lastly, I dont totatally understand your last point re: individ tables for individ students. Im not creating a separate table for each individual student if thats what your talking about.

- By the way, I actually figured out my original dilemma -> But it would be interesting to know your views on the questions I have brought up.

Thanks in advance, David.

 

[jrbarnett]

David

Is there code for finding out if a row does not exist? Yes

Run a query:

Select strStudentID, strSubject, intPercentage
From strStudentMarks
Where strStudentID = '1234' And strSubject = 'Maths'

If no rows are returned, there is no mark. You can use the DCount function to test for 0 rows, or the DLookup function to test for a Null value in VBA code which will have the same effect as SQL returning 0 rows.

As for individual student tables, in your initial post you say "I need to make an update query to place scores from Course Reports, to Individual Student Tables" - Perhaps I misunderstood, this request.

John