Calculation Questions

[SurvivorSteph]

New user here...



I need to determine the number of test attempts a particular student has left.

Field names are:

Sore (this is the score for the initial exam)
Score_t1r1 (this is the first retake)
Score_t1r2 (this is the second retake)
Score_t1r3 (this is the final retake)

One student may not need all three retake attempts; however, I need to calculate how many retake tries are available. Please note that one or more of these fields may have scores in them, so do I need to use a count or null feature? Also, I need to be sure that the returned value is for one student.

Hopefully I've covered all of my variables. If more information is needed, I can provide it.

Thanks for the help!

[maximus]

I assume the following:

1) Each student Get three chances.
2) You want to calculate how many Chances used.

iif(isNull(DCount("[Score_t1r1]","Table_Name","[Student_ID]=1")),0,DCount("[Score_t1r1]","Table_Name","[Student_ID]=1"))
iif(isNull(DCount("[Score_t1r2]","Table_Name","[Student_ID]=1")),0,DCount("[Score_t1r2]","Table_Name","[Student_ID]=1"))
iif(isNull(DCount("[Score_t1r3]","Table_Name","[Student_ID]=1")),0,DCount("[Score_t1r3]","Table_Name","[Student_ID]=1"))

What Happens here:
1) The Score count of each Retake is calculated for student with ID = 1. If Score is not in Entered 0 is returned.
2) U will notice I have used student Id as criteria in the DCount.


How to use it:

Assuming you have a student_table

make a query using the table and then use this DCounts as Calculative Fields;

Change the Criteria of the DCount Like:

DCount("[Score_t1r1]","Table_Name","[Student_ID]=" & [tblStudent].[Student_id])

Now adding this Calculative Fields will give u how many retakes utilised.