Lost on how to average a record that may contain blank fields

[fateddarkness]

I do not understand the solutions I'm being given. I've tried to follow your advice and the instructions from the link you provided, June and I've tried the suggestions you've had for me, Linq, but I'm too new at this. I know you guys cannot take the time to explain step-by-step, so I'm going to learn VBA since it seems like it's the only option I have here. I'll revisit this issue in a couple days once I have a foundation to work from and try again.



Thanks for the assistance, and I hope if I still have questions you'll be willing to help later.

[June7]

Does this help:

1. Create a general module in the VBA editor: Insert > Module

2. Copy/paste into the module all the functions from item 4 of the link I provided. Here it is again http://support.microsoft.com/kb/209839

3. In query or in textbox on form or report use the expression I show in earlier post. Here it is again:
RAvg([1A], [1B], [1C], [1D], [1E], [1F], [1G], [1H], [1I], [1J], [2A], [2B], [2C], [2D], [3A], [3B], [3C], [3D], [3E], [3F], [3G], [4A], [5A], [5B], [5C], [5D], [5E], [5F], [5G], [5H], [6A], [6B], [6C], [7A], [7B], [7C], [7D], [7E], [7F], [7G], [7H], [7I], [7J])

[fateddarkness]

Does this help:

1. Create a general module in the VBA editor: Insert > Module

2. Copy/paste into the module all the functions from item 4 of the link I provided. Here it is again http://support.microsoft.com/kb/209839

3. In query or in textbox on form or report use the expression I show in earlier post. Here it is again:
RAvg([1A], [1B], [1C], [1D], [1E], [1F], [1G], [1H], [1I], [1J], [2A], [2B], [2C], [2D], [3A], [3B], [3C], [3D], [3E], [3F], [3G], [4A], [5A], [5B], [5C], [5D], [5E], [5F], [5G], [5H], [6A], [6B], [6C], [7A], [7B], [7C], [7D], [7E], [7F], [7G], [7H], [7I], [7J])

I had to shorten the expression because Access says it's too complex and still had to use an equal sign. But yes, it's working for me. Thank you!


There's probably a better way to do it, but as a workaround I placed each of the seven categories in a separate text box, gave them custom names, then used the formula to average those scores like this:

=RAvg([Score1], [Score2], [Score3], [Score4], [Score5], [Score6], [Score7])

Thanks again for the assistance!

[June7]

So you average 7 averages?

Yes, use = sign in textbox but not in query.

I am surprised by the too complex error. I count 221 characters in that expression and I think limit for ControlSource is 255.

However, glad you have a working solution. An alternative would be a custom function to handle this specific situation and would involve opening a recordset and looping through the recordset fields.

Still, the 'better' way is to normalize data.

[fateddarkness]

Yes, basically averaging averages. I just needed to show my co-worker I was making progress. However, we did notice that the function is still counting blanks in the total. An average for one category is 25% when it should be 40%. I'm guessing I'll need to add the RCount function somewhere.

Not sure how to make a custom function or what a recordset is, but I will eventually!

Edit: I'm also looking into how to normalize my data. I thought I was doing a fairly good job of keeping related info on separate tables, but I'm sure there's room for improvement.

[June7]

I use the RAvg function and Null values are not included in the count for the average calc. Don't know what it is about your data.

It is a balancing act between normalization and ease of data entry/output. At some point you might scream 'ENOUGH!'. I have a db that is not strictly normalized, hence the need for RAvg function. However, the most items I have for one average is 5.