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?
7 1816 NeoPa 32,556
Expert Mod 16PB
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 5,501
Expert Mod 4TB
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)]"
@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 32,556
Expert Mod 16PB
Good luck. Let us know how you get on or if you need some further nudges in the right direction.
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" zmbd 5,501
Expert Mod 4TB
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 32,556
Expert Mod 16PB 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John |
last post by:
If anyone can help me out with a good way to do this in javascript I
would greatly appreciate it.
I need to compute three dates in javascript - all of which relate to a
given date, say todays...
|
by: Neeper |
last post by:
Hi,
I've got a transactions table with a tstamp field (datetime type) and
I need help creating a SELECT query to retrieve only last transactions
that occured within the last 5 minutes. The...
|
by: Laguna |
last post by:
Hi Gurus,
I want to find the expiration date of stock options (3rd Friday of the
month) for an any give month and year. I have tried a few tricks with
the functions provided by the built-in...
|
by: Terri |
last post by:
I have a table called Transactions with 3 fields: ID, Date, and Amount. Each
ID can have multiple transactions in one particular year. An ID might not
have had any transactions in recent years.
...
|
by: rjfjohnson |
last post by:
Hey,
Today is Thursday 16-Feb-06. The same thursday last year is 17-Feb-05.
Because I am comparing daily sales between years, I need to know the
date of the same weekdayname as last year, so...
|
by: Johan Mcgillicutty |
last post by:
Could use some help on this one-
I’m new to MS Access, and even newer to SQL. I’m building a database for clients of a treatment center, and need to keep track of when people are due for their...
|
by: pratapmysql |
last post by:
Hello All,
I am trying to get same day (name) of week of last year in this case date of day is not constraint eg. if december 2007-12-19 day is wednesday ,then last year 2006 wednesday for same is...
|
by: Supermansteel |
last post by:
I have a Query/Report that I am running a Date Parameter so it only pulls in the information for the End of Month Results for that person. It runs the Avg for everyone for that month also. I was...
|
by: Mike P |
last post by:
How would I check a datetime variable is within the last 7 days?
*** Sent via Developersdex http://www.developersdex.com ***
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |