By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Calculate Avg By Month

P: 2
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
Jul 29 '10 #1

✓ answered by 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

Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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
Jul 30 '10 #2

P: 2
@zepphead80
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
Jul 30 '10 #3

patjones
Expert 100+
P: 931
It's no problem at all. I'm glad you got it working.

Welcome to BYTES!

Pat
Jul 30 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.