There is life in the old dog yet

[drisconsult]

I have recently been having detailed conversations with the fundi's (Swahili for expert) at Access Forums concerning calculating a rolling average when the denominator was unknown. I have to be honest and say that most of the replies were beyond my Visual Basic levels.
However, I keep a record of everything I have written and suddenly remember that I had a similar problem in 1998 with Access 97 and had resolved it. And I have resolved the problem, although I do not think that purist would agree with my solution. So here it is.

I created this expression on the 14 fields where I had to enter a number.
=IIf([LSUB01] Is Not Null And [LSUB01],1,0) x 14
I then added these fields together as shown on the graphic.
The third graphic shows the result I received.. My expression has successfully counted the fields with data in them, which in this case is 12 and given me the correct average. The fourth screenshot shows the correct answer when 14 subjects are taken.
The fourth screenshot also highlights a problem I cannot resolve. The answer has shown fifteen decimal places, when it should only show 1. I have every combination possible to eliminate this problem but have failed. When only the answer is shown, there is no problem, but the moment it is combined with text, I see the huge number of decimal place. The white answer showing the count will be hidden on the final form.
I would be very grateful for a solution to this problem.
Terence Driscoll
London

 

[The_Doc_Man]

Since the textbox under the "student has achieved a score of nn" would not be uniform in format (sometimes having text, sometimes not), you might do best to leave it as an unbound text box and use an IF-THEN-ELSE sequence in the form's OnCurrent event to fill it in. Then in one leg of the IF, use a simple format statement like Format( ScoreAverage, "##.#") - but in the other leg, use "Average Score " & CStr( ScoreAverage ). This would work because the CStr(double-precision) will provide as many digits as there are. If it happens to come out even, you get shorter strings, but if it is a long, ugly fraction you would get a long, ugly fraction. Whereas the FORMAT statement with a shorter template will stick to the template's format of nn.n as your result.

 

[drisconsult]

Since the textbox under the "student has achieved a score of nn" would not be uniform in format (sometimes having text, sometimes not), you might do best to leave it as an unbound text box and use an IF-THEN-ELSE sequence in the form's OnCurrent event to fill it in. Then in one leg of the IF, use a simple format statement like Format( ScoreAverage, "##.#") - but in the other leg, use "Average Score " & CStr( ScoreAverage ). This would work because the CStr(double-precision) will provide as many digits as there are. If it happens to come out even, you get shorter strings, but if it is a long, ugly fraction you would get a long, ugly fraction. Whereas the FORMAT statement with a shorter template will stick to the template's format of nn.n as your result.

Thank you so much for the eagerly awaited advice. Keep safe and well. Will let you know how I get on. Terence

 

[Pat Hartman]

The reason this has been so difficult is because your data is not normalized. The underlying data is laid out as a spreadsheet rather than as a relational database. When your columns are rows as they should be, the SQL functions automatically know the denominator and you don't need to count the populated fields to figure it out.

Sometimes excel people get abscessed with presentation and can't see past it and so they insist on recreating their spreadsheet in a table and don't understand why this type of calculation is so difficult. Everything is difficult when you don't use your tool correctly.

You have "solved" this problem but you will always have others. what are you going to do if you need more than 14 subjects for example?

If you care to normalize the database, start a new thread and post the tables.