Average for Each Day of the Week 1

[xeb]

In Access 2000, in a report, I need to calculate the average for each day of the week. In other words, I need the average of all "Monday" records, all "Tuesday" records, etc.

What would the expression be?

 

[PaulBricker]

Assuming you have a date field you can try something like this as the Control Source for a textbox in the Report Footer.

=Sum(IIf(Format([DateField],"dddd&quot = "Monday",[FieldToSum],0))/Abs(Sum(Format([DateField],"dddd&quot= "Monday&quot)

This gives you the SumOfFieldToBeTotaled/CountOfFieldToBeTotaled

which it think gives you the average. You might clean it up using the Weekday() function like this

=Sum(IIf(Weekday([DateField]) = 3,[FieldToBeSummmed],0))/Abs(Sum(Weekday([DateField])=3))

You would need one for each day of week.

Paul

 

[xeb]

Thanks Paul!

That worked great selecting the desired day and providing a sum of all the records for that day, but it did not average the records.

This is a copy of what I used:

=Sum(IIf(Weekday([Date])=3,[T1-P1]+[T1-P2]+[T1-P3],0))/Abs(Sum(Weekday([Date])=3))

How should it be modified to average [T1-P1]+[T1-P2]+[T1-P3]?

Thanks again for your help.

 

[PaulBricker]

We'll have to investigate it a little. First, is [T1-P1] a Field name? Is it a calculated field?
Second, try this expression in the Control Source for a textbox
=Sum(IIf(Weekday([Date])=3,(,[T1-P1]+[T1-P2]+[T1-P3],0))
and see what you get
also try
=Abs(Sum(Weekday([Date])=3))
How do these expression perform? Do they return the correct values?

Paul

 

[xeb]

Thanks for answering so fast!

[T1-P1], etc. is a field name. It is not a caluculated field.

Okay, I'll try those expressions.

Thanks again, I really appreciate your help.

 

[PaulBricker]

Let me know if each works individually or if one doesn't work. That will tell us a lot.

Paul

 

[xeb]

Neither one worked.

The first one returned the following error message:

"Invalid Syntax - You may have entered a comma without a preceding value or identifier."

The second one returned an unknown value on the report.

Like I said yesterday, the original expression you wrote finds the correct day of the week and the correct sum, it just won't average.

Thanks

 

[PaulBricker]

The first expression has an extra comma in it after the [Date]=3 part. This one looks better. Try it and see if you get the correct sums.

=Sum(IIf(Weekday([Date])=3,([T1-P1]+[T1-P2]+[T1-P3],0))

The other one should return a count of the number of records where the day is Monday. When you say it returns an unknown value what do you mean?

Paul

 

[xeb]

Thanks, I'll try that one.

It returned a number on my report but it wasn't the correct average.

Thanks again.

 

[PaulBricker]

The top number (expression) should be the Sum of Values and the bottom number should return the Count of Weekdays. Then the division should give you the average.

Paul

 

[xeb]

Thanks for staying with this one. I really appreciate your help.

My other fields (that don't calculate on specific days of the week) use:

=(Sum([T1-P1])+Sum([T1-P2])+Sum([T1-P3))/(Count([T1-P1])+Count([T1-P2])+Count([T1-P3]))

I was wondering, if we added the "IIf(Weekday([DateField]) = 3" to this in some way, would it work?

Thanks again!

 

[PaulBricker]

You could wrap the whole thing in the Weekday function something like this
=IIf(Weekday([Date])=3,(Sum([T1-P1])+Sum([T1-P2])+Sum([T1-P3))/(Count([T1-P1])+Count([T1-P2])+Count([T1-P3])),0)

Try it and let me know if this is what you need.

Paul

 

[xeb]

I worked on it for three hours today and I still can't get it to work.

The following one returns a value of “0”, “1”, etc. depending on what the last digit of the expression is:

=IIf(Weekday([Date])=3,(Sum([T1-P1])+Sum([T1-P2])+Sum([T1-P3))/(Count([T1-P1])+Count([T1-P2])+Count([T1-P3])),0)

The following one returns the exact "Sum" of the indicated fields for the selected day of the week.

=Sum(IIf(Weekday([Date])=3,([T1-P1])+([T1-P2])+([T1-P3])))

However, no matter what I try, I can't get it to average.

There must be a way, right?

As always, thanks.

 

[PaulBricker]

Let me step back a little and find out some more info. First, What are the values in the fields [T1-P1], [T1-P2] etc. Also, how is a record set up. I know you have a field [Date] what else is in a record. I'm missing something simple here. Let me know and we'll get there. If that doesn't work you might think about emailing it to me.

Paul

 

[xeb]

I have a table with a date field and quite a few other fields that are labeled [T1-P1], etc. The values in these fields are and will always be either a 1, 2, or a 3, nothing else.

If you need anything else just let me know.

Thanks for your continuing help and offering to have it e-mailed to you if necessary, although that might be difficult because it's on a computer that isn't set up for e-mail and the file is too big for a disk, but if that's what it takes, I'll find a way to do it.

Thanks again.

 

[PaulBricker]

Well the problem is in the Count of the fields returned. If it's possible, then the easiest thing is to just mulitiply the Count(Weekday(Date)=2)* NumberofFields so if you have the fields [T1-P1], [T1-P2]and [T1-P3] then the expression would be
Count(Weekday(Date)=2)* 3

This would give you the correct count to divide by. So your whole expression would be

=Sum(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3])))/Count(Weekday(Date)=2) * 3

Let me know if this will work for you. If not, I have another expression

=Abs(Sum(IIf(Weekday([Date])=2,[T1-P1]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P2]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P3]<>0,0)))

that will work as the divisor. It's just that if you have a lot of fields the expression gets long.

=Sum(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3])))/Abs(Sum(IIf(Weekday([Date])=2,[T1-P1]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P2]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P3]<>0,0)))

Let me know what you think.

Paul

P.S. I discovered that I had used Weekday(Date) = 3 to return Monday. It's Weekday(Date)=2 that returns Monday.

 

[xeb]

I think we made some progress.

The following expression works:

=Sum(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3])))/Count(Weekday(Date)=2)/3

However, I have 40 fields and it stops working at 26 or 27 fields. I get an Error# on the report. It will not calculate more than 26 or 27 fields.

This is the one that works with 26 fields:

=Sum(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3]+([T1-P4]) +([T1-P5])+([T1-P6])+([T1-P7])+([T1-P8])+([T1-P9])+([T1-P10]) +([T2-P1])+([T2-P2])+([T2-P3])+([T2-P4])+([T2-P5])+([T2-P6])+([T2-P7])+([T2-P8])+([T2-P9])+([T2-P10]) +([T3-P1])+([T3-P2])+([T3-P3])+([T3-P4])+([T3-P5])+([T3-P6]))))/(Count(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3]) +([T1-P4]) +([T1-P5])+([T1-P6])+([T1-P7])+([T1-P8])+([T1-P9])+([T1-P10]) +([T2-P1])+([T2-P2])+([T2-P3])+([T2-P4])+([T2-P5])+([T2-P6])+([T2-P7])+([T2-P8])+([T2-P9])+([T2-P10]) +([T3-P1])+([T3-P2])+([T3-P3])+([T3-P4])+([T3-P5])+([T3-P6]))))/26

Why does it stop calculating at 26 or 27 fields?

So then I tried this expression:

=Sum(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3])))/Abs(Sum(IIf(Weekday([Date])=2,[T1-P1]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P2]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P3]<>0,0)))

It works, but when I add my 40 fields it is too long, but only by a couple of fields.

Is there anyway to shorten this expression? The only way I can think of is to rename my fields which, depending on how I do it, might save just enough to make it work, but it seems like there must be a better way.

If you need more information about my results, let me know.

Thanks for your continuing help. I'd be lost without you.

 

[PaulBricker]

The IIf is probably only capable of handling 26 fields. Try breaking it into multiple IIF's like this

=Sum(IIf(Weekday(Date)=2,[T1-P1]+[T1-P2]+.......[T3-P6])) + Sum(IIf(Weekday(Date)=2, [T3-P7] + [T3-P8]+.....[LastOne]))/Count(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3]) +([T1-P4]) +([T1-P5])+([T1-P6])+([T1-P7])+([T1-P8])+([T1-P9])+([T1-P10]) +([T2-P1])+([T2-P2])+([T2-P3])+([T2-P4])+([T2-P5])+([T2-P6]))+ Count(IIf(Weekday(Date)= 2,([T2-P7])+([T2-P8])+([T2-P9])+([T2-P10]) +([T3-P1])+([T3-P2])+([T3-P3])+([T3-P4])+([T3-P5])+([T3-P6]...[LastOne]))))/40


Paul

 

[xeb]

This is the expression that finally worked.

=Sum(IIf(Weekday([Date])=2,([T1-P1])+([T1-P2])+([T1-P3])))/Abs(Sum(IIf(Weekday([Date])=2,[T1-P1]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P2]<>0,0))+Sum(IIf(Weekday([Date])=2,[T1-P3]<>0,0)))

I had to rename my 40 fields to 1-40 so it was short enough, but it seems to work fine.

The reason it is too long is because the second half of it, the &quot;Abs&quot; part, requires each field to repeat the &quot;Sum(IIf(Weekday([Date])=2&quot;. Would it work the way it is on the first half, the &quot;Sum(IIf(Weekday([Date])=2&quot; once, then all the fields?

Thanks so much for all your help. I never would have been able to do it without you.

Now I have another problem. I need the average for two month periods. Using what you gave me for the other one, I wrote this expression.

=Sum(IIf(Month([Date])=11,([1])+([2])+([3])))/Abs(Sum(IIf(Month([Date])=11,[1]<>0,0))+Sum(IIf(Month([Date])=11,[2]<>0,0))+Sum(IIf(Month([Date])=11,[3]<>0,0)))

It works fine for one month, in this case November. How can I add December. I tried it several ways and it won't work.

Thanks again.

 

[PaulBricker]

Change the expression to include 12
=Sum(IIf(Month(Date)= 11 Or Month(Date)=12,1+2...etc.


Paul