Trying to average a parameter for each month of each year

  • Thread starter Thread starter CJ in VN
  • Start date Start date
C

CJ in VN

I have a set of chemical parameters (pH, EC, Ca, Mg etc...) which I am trying
to get averages of over each month and year they have been sampled for (data
is from 1995 to now with multiple samples taken within each month). My date
format is currently date/time in short format (Access 2000). What is the code
for my query to enable the average of each parameter over each month for each
year to be calculated? (so I want the average value of pH for Jan 1995, Feb
1995, Mar 1995 - Nov 2009).
I realise I will have to do it for each parameter (pH, EC, Ca, Mg etc)
separately. Do I need to change my date format or can I do the query as is?
I am new to Access so please go easy on the tech-speak.
Thanks in advance,
 
It would help us help you if you gave us some information on your data structure.

For instance, do you have one field for each parameter or do you have a
structure like

YourTable
ChemicalParam (text field containing type of parameter pH, EC, Ca, Mg, etc)
ReadingDate (Date field containing date of reading)
Reading (number field containing the numeric value of the reading)

IF you have that structure then you could use a totals query that you can
build in query design view

== Open a new query
== Select your table
== Add the three fields to the query
== Add the ReadingDate field a second time
== Change the first reading date field to
TheMonth: Format([ReadingDate],"yyyy-mm")
== Select View: Totals from the menu
== Change GROUP BY to Avg under the Reading field
== Group by To WHERE under the second ReadingDate field
== Specify a date range in the criteria For instance
Between #1/1/2006# and #12/31/2008#
== Run the query

IF you don't want to limit the date range the you don't need to add the second
instance of the readingDate field


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
OH, IF your structure is more like

ReadingDate (the date of the reading)
One field for each parameter that contains the numeric value for that
parameter, you can build a query that looks like

== Create a new query
== Add your table
== Add all your fields to the list
== Change the ReadingDate field to
TheDate: Format([ReadingDate],"yyyy-mm")
== Select View: Totals from the menu
== Change GROUP BY to Avg under all the chemical parameter fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Do I need to change my date format or can I do the query as is?
Format is a matter of display. If you date field is datatype DateTime then
it does not matter.
Post the complete table structure you are using to collect the data - table
and field names with datatype. Post sample data.
 
As others have pointed out, "how" depends on "what".

If you don't give us an idea of what your data structure looks like, we can
only guess how you can do what you're asking...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top