Not sure if this belongs in Queries/Form/Reports because it has to do with calculating formulas within all of the above but here goes anyway:

1- I've collected data that is entered by week number via a form.
2- The data utilizes fields that are to be used in calculating formulas, which I am familiar with.
3- I WANT to average the most recent 2-x weeks, (primarily, I will use the most recent 3 but would like the ability to calculate any given number), of the CALCULATED FORMULAS mentioned above to be used in reports/queries.
4- In other words for example, field a/ field b = X for one given week. Now I want to know the average of field a/ field b for the most recent x weeks.

Is there a way to perform this action? If so, can anyone help me on completing it?

Thanks,
Doug

Forgot to mention that I would like to be able to perform the averaging I described as a whole and in part, (i.e.- the entire table/portions/individuals).

Thanks,
Doug

3- I WANT to average the most recent 2-x weeks, (primarily, I will use the most recent 3 but would like the ability to calculate any given number), of the CALCULATED FORMULAS mentioned above to be used in reports/queries.I'm not sure I understand the "2-x weeks" idea you are talking about.4- In other words for example, field a/ field b = X for one given week. Now I want to know the average of field a/ field b for the most recent x weeks.So, really, you are performing 2 calculations here? Getting the quotient between two fields and then using that number for the number of weeks you want to query? Right?

All of this is possible of course, but you need to clarify your statements, if you will. Thanks! :)

I don't think the attached file will help, but since I can't tell exactly what you want to do, it may give you a little insight on averaging. It shows how to produce the average of field averages, two different ways. Sorry if it's off course, but it's a little interesting. :cool:

I'll try to provide an example as seen in my current database:

Category Week Field1 Field 2 Calc Field
A 1 101 29 (field 1/ field 2) = 3.48
A 2 84 31 (field 1/ field 2) = 2.71
A 3 204 34 (field 1/ field 2) = 6.0

The above represents a calculation done in the calc field, which I am familiar with doing.

Now what I want to query is all 3 weeks together which would entail adding Field 1 for weeks 1 through 3 and the same for field 2 and then performing the same calculation:

Field 1 (sum) Field 2 (sum) Calc field
101 + 84 + 204 = 29 + 31 + 34 = 4.14 (389/94)
389 94

So the 3 week average query for Category A = 4.14

This is what I would like to do in an automated query for any given category, (for which there are 30) USING the MOST RECENT 3 WEEKS, (or if possible, any provided week numbers such as the most recent 5, or 1, 3, and 8 and son on).

Hope this clears things up and some help can be provided.

Thanks,
Doug

I'll try to provide an example as seen in my current database:

Category Week Field1 Field 2 Calc Field
A 1 101 29 (field 1/ field 2) = 3.48
A 2 84 31 (field 1/ field 2) = 2.71
A 3 204 34 (field 1/ field 2) = 6.0

The above represents a calculation done in the calc field, which I am familiar with doing.

Now what I want to query is all 3 weeks together which would entail adding Field 1 for weeks 1 through 3 and the same for field 2 and then performing the same calculation:

Field 1 (sum) Field 2 (sum) Calc field
101 + 84 + 204 = 29 + 31 + 34 = 4.14 (389/94)
389 94

So the 3 week average query for Category A = 4.14

This is what I would like to do in an automated query for any given category, (for which there are 30) USING the MOST RECENT 3 WEEKS, (or if possible, any provided week numbers such as the most recent 5, or 1, 3, and 8 and son on).

Hope this clears things up and some help can be provided.

Thanks,
Doug

Now what I want to query is all 3 weeks together which would entail adding Field 1 for weeks 1 through 3 and the same for field 2 and then performing the same calculation:

Field 1 (sum) Field 2 (sum) Calc field
101 + 84 + 204 = 29 + 31 + 34 = 4.14 (389/94)
389 94

So the 3 week average query for Category A = 4.14Lukas,

You can do this by this query...SELECT category
(SUM(field1) / SUM(field2)) AS [newfield]
GROUP BY categoryThis is what I would like to do in an automated query for any given category, (for which there are 30) USING the MOST RECENT 3 WEEKS, (or if possible, any provided week numbers such as the most recent 5, or 1, 3, and 8 and son on).I can't believe this question, and my idea is probably off the wall, but I know for a fact that it will work. It may be a better idea to work with an array, if that's possible. But, if you want to try, here it is...

1) Create a temporary table to hold the appended records.
2) Loop through a RunSQL command on a Form button that takes a number (number of Recent weeks desired) from a Form text box and appends records, one group at a time, to the temporary table, based on the week number of the loop cycle. Say you want to append five weeks of most recent records for each category. The query would look like (temp = temporary holding table)...Button_Click

Dim x as Long

x = Forms!YourForm!TextBox

Do Until Forms!YourForm!TextBox = 0

DoCmd.RunSQL "INSERT INTO temp (all relevant fields) " & _
"SELECT (all relevant fields) FROM table " & _
"WHERE table.week = Forms!YourForm!TextBox"

Forms!YourForm!TextBox = Forms!YourForm!TextBox - 1

Loop

Forms!YourForm!TextBox = xThe red stuff keeps the value of the textbox the same as the number that was initially typed in, so you don't see a 0 in the textbox when the code stops running.

Anyway, when that is done, you would have to query the temporary table from the first query I wrote. This is not perfect, because if you were to say, enter the number '5' in the textbox, you would see even those categories in the query that only have, say, 3 weeks on record, but you would still get a total (quotient of field1 and field2) for them. To limit the query output to just those categories that have the specified number of weeks of data that you want, you would have to use DLookup before the loop starts to see which records you wanted. Also, turning the warnings off would be a good idea too. ;)

You said that you want it automated? Well, all of the code that needs to run can be done in one event by adding your DoCmd.OpenQuery command at the end of all this. So, after clicking the button, you will see the query pop with all the information you need.

This is totally not efficient, but what a way to work it... :)