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

[sbhatia]

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?

[NeoPa]

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 :

([DateField] Between DateAdd('yyyy',-1,Date()) And Date())

Last 4 months by :

([DateField] Between DateAdd('m',-4,Date()) And Date())

So, one of your fields wants to be :

IIf({Last4M},[Projected],0) AS [Projected4M]

While another wants to be :

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.

[zmbd]

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 "ProjectedSolar OutputkWh" or Projected_Solar _Output_kWh" finally, if you insist on the spaces and non-alphas "[Projected Solar Output (kWh)]"

[sbhatia]

@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

[NeoPa]

Good luck. Let us know how you get on or if you need some further nudges in the right direction.

[sbhatia]

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 "ProjectedSolar OutputkWh", "ActualSolarOut put", and "Performanc e" 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"

[IMGnothumb]http://i58.tinypic.com/2uzytc3.png[/IMGnothumb]

[zmbd]

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.

[NeoPa]

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.