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

How do I find the average of entries within the last 4 months, and last year, separat

P: 3
Microsoft Access beginner here...
I work as an intern for a bank that loans money to alternative energy companies.
I have created a data table for one specific solar project, called "Dement".
The fields within this table in question are "Date", "Projected Solar Output (kWh)", and "Actual Solar Output (kWh)".
I want to find the average for "Projected" and "Actual" for the past 4 months, and then the past year, respectively.
These values should then feed into a form or report where they can be compared to other projects and companies
How can I go about finding this average?
Jun 24 '15 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,769
First build the query that returns the values you want to aggregate. The WHERE clause (Filter) can ensure only the last year is included, but you want one of the result fields to include only data relevant to the last four months.

Last year can be specified as :
Expand|Select|Wrap|Line Numbers
  1. ([DateField] Between DateAdd('yyyy',-1,Date()) And Date())
Last 4 months by :
Expand|Select|Wrap|Line Numbers
  1. ([DateField] Between DateAdd('m',-4,Date()) And Date())
So, one of your fields wants to be :
Expand|Select|Wrap|Line Numbers
  1. IIf({Last4M},[Projected],0) AS [Projected4M]
While another wants to be :
Expand|Select|Wrap|Line Numbers
  1. IIf({Last4M},1,0) AS [4MCount]
Now all you have to do is calculate the various 4M averages using SUM()s of the 4M fields divided by [4MCount]. The yearly average can be easily calculated using the AVG() itself.
Jun 25 '15 #2

zmbd
Expert Mod 5K+
P: 5,397
Note: "Date" is a reserved keyword in Access and many other databases and other code. As such, it is discouraged from being used as a field/table name:
Problem names and reserved words in Access : Date.

Also, using spaces and special characters in the field names is discouraged, although allowed. You will find that when you go to write queries, macro, and vba scripts that the spaces and non-alphanumerics can create some interesting pitfalls. I suggest replace spaces with the underscore or use the cap method ("Projected Solar Output (kWh)" becomes "ProjectedSolarOutputkWh" or Projected_Solar_Output_kWh" finally, if you insist on the spaces and non-alphas "[Projected Solar Output (kWh)]"
Jun 25 '15 #3

P: 3
@zmbd duly noted, I changed the field names yesterday, it does run more smoothly
@NeoPa I will try that and see what happens, thank you. I'll post a reply after
Jun 25 '15 #4

NeoPa
Expert Mod 15k+
P: 31,769
Good luck. Let us know how you get on or if you need some further nudges in the right direction.
Jun 30 '15 #5

P: 3
I'm still confused, and my query isn't working right.
This is what I have.
I want the average of the latest four months (2/28/2015-5/31/2015) for the categories "ProjectedSolarOutputkWh", "ActualSolarOutput", and "Performance" displayed separately in 3 different queries.
Then I want to do the same thing for the latest year (5/31/2015-5/31/2014)
Can anyone show me the SQL code that would make this work?
The table name is hidden for client privacy but assume it's called "Table1"

Jun 30 '15 #6

zmbd
Expert Mod 5K+
P: 5,397
open your query in design mode
right click in the table area
select sql view
copy and paste that here...
Please format the SQL script using the [CODE/] button in the post toolbar... this format is required.
Jun 30 '15 #7

NeoPa
Expert Mod 15k+
P: 31,769
SBhatia:
I'm still confused, and my query isn't working right.
That's fine, but we'll need to see what you have in order to be able to help you. Please follow ZMBD's instructions for that.

I suspect we can help you understand all confusions you have, but in order to do so we need you to explain them clearly to us. That, or by showing us your existing SQL, illustrate where it is you're struggling.
Jul 1 '15 #8

Post your reply

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