Calculate Avg By Month

[Daniel Key]

Hello All,

I have searched for this all over without success.

I am using Access 2007 and I have a table named tblReadings with 4 fields, Name, Date, Reading1, Reading2. The data is as follows:

Name Date Reading1 Reading2
Smith 7/1/2010 150 45
Smith 7/2/2010 250 65
------Readings for each day of the month-------
Smith 8/1/2010 47 0
Smith 8/1/2010 79 14
Jones 7/1/2010 150 45
Jones 7/2/2010 250 65
------Readings for each day of the month-------
Jones 8/1/2010 47 0
Jones 8/1/2010 79 14

-------Etc.----------------

I need to calculate the Average of Reading1 for each month and for each Name and display it in a Report.

I currently use Between [Start Month] AND [End Month] on an Expression Month([Date]) and =[Enter Name] on Name in a query to get the Name and Months I need to average.

I am using in my report a text box (txtAvgReading1 ) with the Control Source set to =Avg([Reading1]). I am getting the Average for all the records in Reading1 for all months. I don't know how to Seperate the months and show each month only once in the report with the averages.

I hope I have given you enough information, please let me know if I haven't and I will try to give you more.

Thanks,

Daniel

[patjones]

In addition to taking the average of Reading1, you need to group the results by name and date.

In the ribbon, if you click on Group & Sort while in report design view, you'll see a panel pop-up below the report. This is where you want to add two grouping levels for name and date. When you do the date grouping, clicking on More... should reveal an option that lets you specify what date interval to group on (week, month, etc.).

This is generally the direction you want to go in, I believe.

Pat

[Daniel Key]

In addition to taking the average of Reading1, you need to group the results by name and date.

In the ribbon, if you click on Group & Sort while in report design view, you'll see a panel pop-up below the report. This is where you want to add two grouping levels for name and date. When you do the date grouping, clicking on More... should reveal an option that lets you specify what date interval to group on (week, month, etc.).

This is generally the direction you want to go in, I believe.

Pat

zepphead80,

Thank You!!! This was driving me crazy. I already had a grouping by Name, however I added a grouping by Date and it was doing the same thing. I played with it a little and found that I had to add a footer section to the Date group and put my calculation in the footer.

It is now working exactly how I want it to. Thanks to you, I now understand more about how that feature works.

Thanks Again,

Daniel

[patjones]

It's no problem at all. I'm glad you got it working.

Welcome to BYTES!

Pat