Adding Pass/Fail and Audit to a school database

[pede0293]

Hello,



I have inherited an Access project from a former employee. Unfortunately, I do not know a lot about Access. Any help you can give me would be wonderful.

My school currently keeps all grades in an Access program. Right now we just assign numerical values and those are turned into grades (I cannot actually find this table). Overall it works well, however, we now have classes where we need to input Pass/Fail and Audit.

Please let me know what information I can give to make answering my question easier. Also, if I need to upload the file to make it easier to understand, please let me know the best way to do this without including private information.

Thank you!
Erika

[June7]

Make a copy of database, remove confidential data (address, phone, etc), run Compact & Repair, zip if still large, attach to post. Attachment manager is below the Advanced post editor window.

[pede0293]

Thanks for the information! I've attached the file.

Make a copy of database, remove confidential data (address, phone, etc), run Compact & Repair, zip if still large, attach to post. Attachment manager is below the Advanced post editor window.

[pede0293]

I've also attached a notepad file I found. Perhaps the code in there can be changed? If so, where would I paste this information in Access?

Thanks

[June7]

The Students And Classes table has the Grade field. You want to input a Pass/Fail/Audit grade into this field? Since it is a number field, I suggest you pick large numbers to represent Pass/Fail/Audit, numbers that cannot be mistaken for normal number grades. Like:

Pass = 997
Fail = 998
Audit = 999

Then in a query or report textbox that displays grade (such as the Class Results Summary) have an expression that will convert those numbers to the words. Like:

IIf([Grade]=997,"Pass",IIf([Grade]=998,"Fail",IIf([Grade]=999,"Audit",[Grade])))

You probably don't want those values to be included in GPA calculation. If there is a GPA calculation on any report or form, identify them and I will look at.

The alternative is to change the Grade field to a text datatype so numbers and alphas can be entered. This approach presents its own set of issues.

[pede0293]

There are some reports with GPA calculations:
Valedictorian
Student Full Transcript
Report Card By Year

Ideally I would like to put Pass/Fail/Audit (or number equivalent) in the Classes Form -> Assignments tab -> View assignments and input the word/number for each student.

Thanks

The Students And Classes table has the Grade field. You want to input a Pass/Fail/Audit grade into this field? Since it is a number field, I suggest you pick large numbers to represent Pass/Fail/Audit, numbers that cannot be mistaken for normal number grades. Like:

Pass = 997
Fail = 998
Audit = 999

Then in a query or report textbox that displays grade (such as the Class Results Summary) have an expression that will convert those numbers to the words. Like:

IIf([Grade]=997,"Pass",IIf([Grade]=998,"Fail",IIf([Grade]=999,"Audit",[Grade])))

You probably don't want those values to be included in GPA calculation. If there is a GPA calculation on any report or form, identify them and I will look at.

The alternative is to change the Grade field to a text datatype so numbers and alphas can be entered. This approach presents its own set of issues.

[June7]

Isn't Pass/Fail/Audit a rating assigned to the class, not individual assignments? So either a student has a number grade for the class or one of Pass/Fail/Audit. This is the Grade field on the Students tab.

Assignments tab doesn't have field for class level info such as class grade. It only shows the percent weight each exam/assignment contributes to the overall grade. The Results table stores the score for each exam/assignment. The Assignments form is where you enter scores for exams/assignments.

So for the GPA calc, you want to exclude the Pass/Fail/Audit classes?

[pede0293]

It is possible that a student would audit a class but not receive a grade, while all others in the class will receive a grade. I don't foresee that being the case with Pass/Fail though.

And for the GPA calc I wouldn't want any audit/pass/or fail to count at all.

[June7]

That confuses me.

A student doing Audit could still get a number grade? But students given a simple Pass or Fail will not receive a number grade?

[pede0293]

Sorry to be confusing - no, one individual student could only get an audit or a number grade (or pass/fail for that matter) but within one class you might have one student that is auditing and another student that is getting a number grade.
Hope that clarifies it!

That confuses me.

A student doing Audit could still get a number grade? But students given a simple Pass or Fail will not receive a number grade?

[June7]

I was making an assumption that Grade field is number type. I just looked at it and it is actually text type. It is ready to receive P/F/A or numbers as values.

I reviewed the queries used to derive the GPA. They don't use the Grade field. The Cumulative Class Percentiles query uses the Scores from Results table to calculate a grade %, that value carries forward into the 3 reports you listed and is the basis for GPA calc.

This means if scores are entered even for classes that are P/F/A, they will be included in the GPA. To exclude those scores, need to adjust the Cumulative Class Percentiles query. Just filtering the P/F/A classes out of the query causes issues in subsequent queries if the student has no other classes. So I adusted the formula for Grade (%). Open the query in SQL View of the query designer and paste this statement in:

SELECT DISTINCTROW [Students And Classes].StudentID, Assignments.ClassID, Sum((IIf(IsNull([Results].[Score]) Or [Grade] In ("P","F","A"),0,[Results].[Score])/[Assignments].[MaximumPoints])*[Assignments].[PercentOfGrade]) AS [Grade (%)]
FROM Assignments INNER JOIN (Results INNER JOIN [Students And Classes] ON Results.StudentID = [Students And Classes].StudentID) ON (Assignments.AssignmentID = Results.AssignmentID) AND (Assignments.ClassID = [Students And Classes].ClassID)
GROUP BY [Students And Classes].StudentID, Assignments.ClassID;

Hopefully nothing else in the project needs to be changed.

[pede0293]

Thanks - I changed the info in the query. Now, how will it work to input Pass/Fail or Audit when entering grades under Classes -> Assignments -> View assignments? What value do I put in?

Thanks so much for the help!

[June7]

Read post #7 again.

The Grade field is on Students tab. It is already set for text entry, presumably A,B,C,D,F. Now you want Pass and Fail and Audit. Use P for Pass and maybe X for Audit.

[pede0293]

I'm sorry, I don't fully understand. I went to Students under Forms and I see the Grade field but it doesn't allow anything to get entered. Isn't this pulling from somewhere else? And does this mean that Pass/Fail/Audit wouldn't be entered under Class like the rest of the grades?

Thanks so much!

Read post #7 again.

The Grade field is on Students tab. It is already set for text entry, presumably A,B,C,D,F. Now you want Pass and Fail and Audit. Use P for Pass and maybe X for Audit.

[June7]

I am referring to the form Classes and the Students tab. I have no problem entering data into the Grade box on Students tab of the Classes form. The entry is saved into Students And Classes table.

The Students form doesn't allow any data entry in the two larger bottom subforms. Both of those subforms are locked up and don't allow any edits or new records, just to display existing records. Only the Awards subform allows entries. The bottom subform shows assignments. The textbox used as a label to display the class name shows #Error when there are no assignments associated with student.