create a total

[human_anomaly]

I'm really stumped on this one. I have a report that is grouped by student. Each student has a number of scores in the detail section labeled test1, test2, test3 etc. There about 40 of these tests. each student will have anywhere from 20 to thirty of these records that contain 40 tests. What I need to do is group the tests (let's say tests 1-10, 11-20, 21-30, and 31-40) into four different groups. I then need to get the total for each group for all the records. I have been able to use a query to do the stuff listed so far; however, it is very tedious. So now I have the totals listed in the student footer, but I also have to list how many points are possible for each group of tests. Each test has a maximum score of 4 so you would think that it would be possible to just create a static number for each group; however, some students are not applicable for certain tests (this N/A setting is stored as a 5). I need to create an if statement that looks like if test1 < 5 then group1Possible.value = group1Possible.value + 4 (where group1Possible is a text box or a label) I can't find out if there is a way to get an if statement in a report. Any suggestions on other ways to accomplish this would be greatly appreciated. Thanks

 

[The_Doc_Man]

If you really have one record with 40 results in it, your database is not normalized, which is part of the reason why you are having such a terrible time of it. On the other hand, if you are happy, I won't chide you for it.

But besides that, it is possible to have a text box for which the controlsource is not a field, but an IIF of a field. (Hint: Look up IIF in Help)