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?

 

[xeb]

I can't get the following expression to work:

=Sum(IIf(Month(Date)= 11 Or Month(Date)=12,1+2...etc.

It works fine for one month, but not for two.

What would the expression be for calculating an average between two dates. I tried the following but it won't work:

Between ‘11/10/03’And ‘1/23/04’

As always, thanks, and happy thanksgiving!

 

[PaulBricker]

OK, the first expression worked for me in my test data. This is what I used (P1, P2 and P3 are my three fields)

=Sum(IIf(Month([Date]) = 11 Or Month([Date]) = 12,P1 + P2 + P3)
(I didn't include the second argument in this IIF statement)

Recheck your expression

Also, I'm not clear on your second question about averaging between two dates. If you filter your report on those two dates, the expressions should reflect that. If you are trying to write an expression like
=Sum(IIf [Date] Between Date1 and Date2,1 + 2...)) then try
=Sum(IIf [Date] >= Date1 and [Date] <= Date2, [1] + [2] + [3]))

Paul

 

[xeb]

I had to change plans.

I had to create a new database just for “Day of the Week” and “Month”. It’s acceptable though because the “Day of the Week” and “Month” report requires only the date and one field, so, although I have to enter the date and the data for that one field, it’s better because I wanted to show all twelve months in the report and there was no way that Access could handle more than five of those expressions. I kept getting the error message “too many fields defined.” Also, I’m now just using the simple “Avg” expression for the one field which I guess is better.

Maybe someday Microsoft will improve Access to be able to handle it all in one database, but for now I guess this is the best solution.

What do you think?

Now I’m having printing problems, but I just printed a bunch of solutions from several different threads, so I’ll try those. But, if they don’t work, and you’re willing, I’ll be back!

One other thing, I have a field that has numbers in it (either 0, 1, 2, 3, or 4). I have it averaged and I have the minimum and maximum for it displayed in my report, but I need to know if there is ever a pattern in the field. In other words, if the number “0” appears continuously for five days, that’s significant to me, and I’d like the report to show that. Is there an expression that would produce such a result or something like it?

Thanks so much for all your valuable help. I have created two good databases (or should I say you did) that will serve my needs well. You’re so patient and it’s so nice to know that people with your knowledge are willing to help.

Thanks again.