459,654 Members | 1,422 Online
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
7 Replies

 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 ([DateField] Between DateAdd('yyyy',-1,Date()) And Date()) Last 4 months by : Expand|Select|Wrap|Line Numbers ([DateField] Between DateAdd('m',-4,Date()) And Date()) So, one of your fields wants to be : Expand|Select|Wrap|Line Numbers IIf({Last4M},[Projected],0) AS [Projected4M] While another wants to be : Expand|Select|Wrap|Line Numbers 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

 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

 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

 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

 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