How to do a rolling average on a query that asks for input

[AJ]

I am probably not going about this the easy way, and I'm sure my database
looks nightmarish to most, but i am having problems calculating a rolling
average.
Here is a breakdown of my database. There are 8 sampling locations, each
with 4 different test results. I need to add the results together for each
location, then choose the summed result that is the highest amoung the 8
locations, and then have a 4-quarter rolling average of these highest summed
results.
I have a form that you input the year and quarter from which you want data.
There is a query for each location that chooses the data, then a union query
puts it all into one column. Then there is a query that selects only the
values that aren't "<". Then there is a query that sums the "real" values.
Then a union query selects the sum from each location. Then another query
chooses the Max sum from the 8 locations.
What I am needing now is to create a rolling average of this final results,
over the course of the last 4 quarters.

 

[KARL DEWEY]

summed result that is the highest amoung the 8 locations,
A couple of questions --
How is the data collected - daily, weekly, monthly? What time period is
being summed to determine highest location?
What is your table and field names? - Datatypes?

 

[AJ]

The table name is "Quarterly Data", and field names are: Date, Location, A,
B, C, D.
There are 16 locations total, divided into 2 groups of 8.

One group is sampled in Quarters 1 & 3, and the other group in Quarters 2 &
4. The 4 parameters being analysed for each location are summed together
(A+B+C+D) for the final result, and the highest result for each quarter is
the number I need to have the 4-quarter rolling average of.

All data types are in text, as there are results that are <0.001 (for
example). I have queries built that select only the data that is not "<"
(but I think these are still text, as that is how they are set out in the
table).

 

[KARL DEWEY]

example).
Do not use text to store values that you will be performing calculations on.
Back up your database and then change the text fields to Number - Single.

I still am not sure what the end results would be. Will it be only a
location with a single number - Quarterly average for that location?

 

[AJ]

Unfotunately I cannot have the data type as numbers, because I cannot type
"<" into a "number" field. Sometimes the results are "<" and sometimes they
are not, so unless there any other data types that will allow me to type in
"<" signs, I'm afraid it'll have to stay as "text".........

In each quarter there is one location that is the highest sum of the
parameters (A+B+C+D) compared to the rest of the locations. In each quarter
you figure out the highest sum (it might be the same location each quarter,
might not be, either way, it must be the highest sum). Then, you take the
highest sum from this quarter and the previous 3 quarters, and average them
to get one result.

Hope that helps........

 

[John W. Vinson]

Unfotunately I cannot have the data type as numbers, because I cannot type
"<" into a "number" field. Sometimes the results are "<" and sometimes they
are not, so unless there any other data types that will allow me to type in
"<" signs, I'm afraid it'll have to stay as "text".........

Please explain: what is the sum of 3.0, 2.5, <1 and >10.

You're insisting on two incompatible requirements: performing arithmatic on a
field, but storing non-arithmatic information.

 

[AJ]

I know, it's not logical, but that's what we're supposed to do. So the "sum"
of 3.0, 2.5, <1 and >10, would be 5.5. It's just the sum of the numbers that
are "real" (without the "<").

So once I build a query to select only the data that is not stored as "<",
then I can perform arithmetic functions on them. I just can't figure out how
to build a rolling average.

 

[John W. Vinson]

I know, it's not logical, but that's what we're supposed to do. So the "sum"
of 3.0, 2.5, <1 and >10, would be 5.5. It's just the sum of the numbers that
are "real" (without the "<").

So once I build a query to select only the data that is not stored as "<",
then I can perform arithmetic functions on them. I just can't figure out how
to build a rolling average.

Try using a query like

SELECT Avg(Val([field])) FROM table
WHERE IsNumeric([field])

The IsNumeric function will return True (and the WHERE clause will retrieve
the record) if it is a number, and the Val() function will convert it to a
number datatype which can be averaged.

 

[AJ]

Thanks, that query is much more simple!

I still have to build a rolling average, so I'm not sure how to do that.
I've tried building a query that will select the data from the previous
quarter (DatePart ("q",-1,[Date])), however, this method does not allow you
to retrieve the 4th quarter data.

I want the average values of the following quarters: 2 (2009), 1 (2009), 4
(2008), 3 (2008)..... and I need this to roll forward every quarter. any
ideas on how to do this?

I know, it's not logical, but that's what we're supposed to do. So the "sum"
of 3.0, 2.5, <1 and >10, would be 5.5. It's just the sum of the numbers that
are "real" (without the "<").

So once I build a query to select only the data that is not stored as "<",
then I can perform arithmetic functions on them. I just can't figure out how
to build a rolling average.

Try using a query like

SELECT Avg(Val([field])) FROM table
WHERE IsNumeric([field])

The IsNumeric function will return True (and the WHERE clause will retrieve
the record) if it is a number, and the Val() function will convert it to a
number datatype which can be averaged.