Average a row MSA2003

[Shaba]

Hello,

I'm having a difficult time finding the answer to my situation. Searched alot but I couldn't find any conclusive solution.

Here is the situation:
I have a table with 32 columns. The first column is populated with client id-s (unique for every client), and the other 31 columns represent one day of the month. I'm trying to create a query that calculates the average of client orders for each client (a row average). One solution yould be a simple SUM function divided by the number of days in each month, but i'm trying to see if there is a function that i can use without SUM/31.

I saw that in Access2000 there was a Ravg function but that does not work in Access2003

Can anyone tell me if there is a function that i can use?

[June7]

That RAvg function was a user-defined function, not an an intrinsic Access function. The code would be the same for Access 2003. See this article http://support.microsoft.com/kb/209839

[orange]

Have you considered a table structure such as:

ClientId
DayOfMonth

You would have a record for each day of the month
You could use an SQL aggregate query to get the average

[Shaba]

Have you considered a table structure such as:

ClientId
DayOfMonth

You would have a record for each day of the month
You could use an SQL aggregate query to get the average

I know it would be better with the Client ID as Columns and the days as rows, but the report is generated automaticly and it would cost alot to change it now.

I will try to implement June7's advice and will get back at you.

Thank you both for replying

[Shaba]

That RAvg function was a user-defined function, not an an intrinsic Access function. The code would be the same for Access 2003. See this article http://support.microsoft.com/kb/209839

This link helped me and I saved the module, so I am able to use the Ravg function now, but i still encounter a slight issue with it:
My table has 33 columns (the 1st is the clientID, and the other 31 are the days in a month, and one for the average). The Ravg function does not work if i try to calculate the average for all 31 colums. The maximum number of columns i could calculate was 28. The expression code is 148 characters long: Val(Ravg([1],[2],[3],...[31])). Is there a limit of characters I can use? Or does anyone now how to compress the expression?

Thank in advance!

[June7]

Wouldn't think 148 characters is too long. What do you mean by 'does not work' - error message, wrong results, nothing happens?

[Shaba]

Wouldn't think 148 characters is too long. What do you mean by 'does not work' - error message, wrong results, nothing happens?

I forgot to specify : the error message is "expression is too complex". And as I said, if I eliminate three of the columns to be calculated, for example i take out from the Ravg([1],[2],[3],[4],...[30],[31]) expression [1],[2],[3] and start it from Ravg([4],[5],[6],[7],...[30],[31]) it's fine! That's what bugs me...

[June7]

I believe the issue is not with the length of the expression but how many arguments are passed in the function. When I use longer field names, can still pass 29 arguments (29 worked for me) but not more.

I think you will have to write a different custom function for your circumstances. I suggest passing criteria such as clientID and any other values needed to filter data. Use the passed criteria to open a recordset in the function. The criteria should limit the recordset to one record of the day fields. Read the values of each field and include in the average calc if a number is encountered.

[Shaba]

Unfortunately i'm not really good with programing and custom functions, but i tried to split the expression in two parts using Rsum and Rcount.

Example:
Rsum1: Rsum([1],[2],[3],...[15])
Rcount1: Rcount([1],[2],[3],...[15])
Rsum2: Rsum([16],[17],[18],...[31])
Rcount2: Rcount([16],[17],[18],...[31])
RSumTotal: =[Rsum1]+[Rsum2]
RcountTotal: =[Rcount1]+[Rcount2]
Average: =[RsumTotal]/[Rcount]

The only thing is i get #Error because there are counts that are NULL and i can't get the iif(isnull()) function to work properly. I think i should use it when calculating the Rsum1 and Rsum2, but somehow i can't integrate the iff(isnull()) function properly.

Any thoughts?

[June7]

Clever!

Try the Nz function.

For the addition, handle Null with:
Nz([Rsum1],0)+Nz([Rsum2],0)
Nz([Rcount1],0)+Nz([Rcount2],0)

But division by 0 will error but Null returns Null, so

[RsumTotal]/IIf([RcountTotal]=0,Null,[RcountTotal])

[Shaba]

I'll give it a try when i get to work because my database is on my work PC and let you know how it went. It's a good thing you mentioned the division by 0 because i didn't take it in to consideration, so, many thanks!!

[Shaba]

June7 ... hat's off!

The only thing missing from the NZ function was the Val(). I added that and it worked. So it's like this:

Count1: Rcount([25],[24],[23],...,[13],[12],[11])
Count2: Rcount([10],[9],[8],...,[28],[27],[26])
Sum1: Rsum([25],[24],[23],...,[13],[12],[11])
Sum2: Rsum([10],[9],[8],...,[28],[27],[26])
SumTotal: =Val(Nz([Sum1],0))+Val(Nz([Sum2],0))
CountTotal: = Val(Nz([Count1],0))+ Val(Nz([Count2],0))
Average: =[SumTotal]/IIf([CountTotal]=0,Null,[CountTotal])

Thank you very much for helping me get out of this pickle!