Adding Pass/Fail and Audit to a school database

[pede0293]

Oh okay. I see this. I put "Audit" in the Grade box and saw it on the Students and Classes table. However, it doesn't pull over to the transcript or the report card reports. It doesn't seem to override what it in the score of the assignments tab.

Thanks!

[June7]

No, Grade is a value for the class not individual assignments. It would not override score on assignments tab as that is a score for specific work assignment. I don't think any of the reports were setup to show Grade field. That would require modifying the design of reports.

[pede0293]

I think I missed something. I just want to enter a word, letter, or number (that changes to a word or letter) that shows Pass/Fail and Audit that will show up on Report Cards and transcripts along with students other grades. They are "grades" so to speak and show what a student has done. So putting Audit in the Grade field but it showing up as an F on their transcript or an A (or whatever numerical value I put under assignments) isn't going to be accurate. So I'm still confused about how to accurately input this information (and get accurate reports).

In Post #4 I believe, I posted a Notepad document with code about grades. Can't I add to that? And if so, what can I add and where can I put/paste it?

Thanks!

[pede0293]

Well...I haven't heard anything, so I'm assuming my question is off the radar now. I just wanted to say thanks for the time you put in to try to help.

[June7]

Did miss post 18 but 19 got my attention.

Forgot to explore that VBA code before. I get it now, you want to derive the letter grade, not have to manually enter it.

Regarding that VBA - if you want to convert assignment scores (or their average for class grade) to letter rating, that function will accomplish. Try:

1. Open the query Cumulative Class Percentiles in design view

2. Construct a new field with expression on the Field row that calls the function (copy/paste this):
GradeLtr: Derive_Grade(Sum((IIf(IsNull([Results].[Score]) Or [Grade] In ("P","F","A"),0,[Results].[Score])/[Assignments].[MaximumPoints])*[Assignments].[PercentOfGrade]))

3. Set the Total row for this field to Expression

4. Open query in datasheet view, see results

This means the Grade field in Students and Classes table is not needed. However, still need to modify reports to show this new field.

[pede0293]

Thanks for the response!

I followed all the steps and I see the change in the datasheet. However, I still don't understand where the information (Pass/Fail or Audit) will ultimately get entered.

I see in the code that I pasted that there is a P, F, and A - what does this correspond to - a number?

I understand now that getting the field to show up in reports is another issue and the next step.

Thanks!

Did miss post 18 but 19 got my attention.

Forgot to explore that VBA code before. I get it now, you want to derive the letter grade, not have to manually enter it.

Regarding that VBA - if you want to convert assignment scores (or their average for class grade) to letter rating, that function will accomplish. Try:

1. Open the query Cumulative Class Percentiles in design view

2. Construct a new field with expression on the Field row that calls the function (copy/paste this):
GradeLtr: Derive_Grade(Sum((IIf(IsNull([Results].[Score]) Or [Grade] In ("P","F","A"),0,[Results].[Score])/[Assignments].[MaximumPoints])*[Assignments].[PercentOfGrade]))

3. Set the Total row for this field to Expression

4. Open query in datasheet view, see results

This means the Grade field in Students and Classes table is not needed. However, still need to modify reports to show this new field.

[June7]

Sorry, I goofed in comment. The Grade field is still needed. With this data structure will have to manually enter P/F/A (or Pass, Fail, Audit). Then use this expression instead of the previous suggestion:

GradeLtr: Derive_Grade(Sum(IIf(IsNull([Results].[Score]),0,[Results].[Score]/[Assignments].[MaximumPoints])*[Assignments].[PercentOfGrade]))

Now also add the Grade field to the query grid. Modify other queries as needed to include the Grade field in the grid. Then the field will be available to the reports to create a textbox for or use in an expression to display either the Grade or the derived letter grade. The expression in textbox ControlSource could be like: = Nz([Grade], [Derived_Grade]). The Report Card By Year report could be modified that way.

[pede0293]

How do I "add the Grade field to the query grid"? Is it a new query?

And for the next part of what you said (Modify other queries...) is that, for example, changing Derived_Grade (in Query Student performance) to GradeLTR:......?

Thanks!

Sorry, I goofed in comment. The Grade field is still needed. With this data structure will have to manually enter P/F/A (or Pass, Fail, Audit). Then use this expression instead of the previous suggestion:

GradeLtr: Derive_Grade(Sum(IIf(IsNull([Results].[Score]),0,[Results].[Score]/[Assignments].[MaximumPoints])*[Assignments].[PercentOfGrade]))

Now also add the Grade field to the query grid. Modify other queries as needed to include the Grade field in the grid. Then the field will be available to the reports to create a textbox for or use in an expression to display either the Grade or the derived letter grade. The expression in textbox ControlSource could be like: = Nz([Grade], [Derived_Grade]). The Report Card By Year report could be modified that way.

[pede0293]

Oh, I didn't mention it, but I did replace the old expression with the new one you gave.

[June7]

Open the query Cumulative Class Percentiles in design view. Drag the Grade field from the Students and Classes box onto the column grid. Do the same in other queries.

Do not change the Derived_Grade calculated field in Student Performance. Drag the Grade field from the Student Subform Subquery box onto the grid.

[pede0293]

There are a couple errors that show up in DesignView - Invalid Control Property:Control Source and Circular Reference

Also, if I try to run the Report Card By Year report a box comes up with Enter Parameter Value Grade. If I don't enter anything then the report comes up with error messages all down one column. If I put in A then there are As all down the column regardless of the percentage.

I'm not sure what to do next...

Open the query Cumulative Class Percentiles in design view. Drag the Grade field from the Students and Classes box onto the column grid. Do the same in other queries.

Do not change the Derived_Grade calculated field in Student Performance. Drag the Grade field from the Student Subform Subquery box onto the grid.

[June7]

The prompt pops up because the Grade field is not included in the RecordSource the report is based on. What change did you make to the report?

DesignView error is where - in Cumulative Class Percentiles query?

A circular reference can happen when a control (such as a textbox on report) has the same name as the field it is bound to. Give controls names different from field, like tbxGrade.

Okay, this has gone on long enough. Here is project with some revisions. The only report I modified is Report Card By Year. There were some quirks that would have been difficult to explain. So explore and see what you think. The Audit/Pass/Fail graded classes are not excluded from the GPA calc (except for the 3 on the report that are presented in labels, not data from the tables).

[pede0293]

Hello,
I'd hoped to get a chance to look through this, but now I am leaving for about three weeks. In that time I won't have my Access file with me. Hopefully I can pick up where we left off when I return?

[pede0293]

Hey!
Thanks for all the help. I looked it over and figured some things out.

The prompt pops up because the Grade field is not included in the RecordSource the report is based on. What change did you make to the report?

DesignView error is where - in Cumulative Class Percentiles query?

A circular reference can happen when a control (such as a textbox on report) has the same name as the field it is bound to. Give controls names different from field, like tbxGrade.

Okay, this has gone on long enough. Here is project with some revisions. The only report I modified is Report Card By Year. There were some quirks that would have been difficult to explain. So explore and see what you think. The Audit/Pass/Fail graded classes are not excluded from the GPA calc (except for the 3 on the report that are presented in labels, not data from the tables).