Computing an Average

[ctrapper]

I have a list of jobs in one column, with corresponding numeric values in another for given dates. Is there a query I can create that would start with the 5th occurrence, and give me a 5 day average as I move down in dates.. ? does this make sense?

[aytee111]

Are you trying to get an average by week? The function DatePart will give you a week number which you can group by and then get an average of the numbers.

[ctrapper]

Hi Aytee.. it's more of running 5 day average.. for example.. after the 5th of the month, going forward I want to average only the last 5 days of data.. it could overlap weeks.. I tried doing it in excel... the only way I know for sure to do it is to filter out all 30 jobs into separate tabs, compute tehe 5 day ave, then combine them all back together.. seems like there's an easier way to do it..

[aytee111]

If you only want the last 5 days - always - then in your criteria for the date column put something like this: >Date()-6 (after today minus 6). I presume you have the date stored?

[ctrapper]

Ok thanks I will try that.. I am not as versed in Access as I am in EXCEL, but I think Access has more flexibility when dealing with data.. I will give it a try!

[ctrapper]

Yes Date is posted in the Table in the format 11/7/2010 for example:

When I enter Criteria as you suggested as: >11/7/2010()-6 I get an error back.. invalid

[John_G]

Hi -

For this type of thing, Excel is far better suited. Access cannot refer to values in any records other than the current one, be it in a recordset, a query, or a form in datasheet view.

You are right in saying that Access has more flexibility, but it's not Excel, and cannot do everything Excel does.

John

[ctrapper]

John I don't disagree at all that's where I started.. I have everything in one tab, and tried to filter by each job, and then do the ave. starting after the 5th occurrence using =subtotal(1,RANGE). However, I cannot copy it down, I guess the filter screws up the copy down. I could create a new for each job, do the ave, and then combine it all back into 1 Spreadsheet.. I was just looking for an easier way where the data is already together..

[aytee111]

If you are going to use a real date in your query, then the format is a bit different.

> DateDiff("d",-6,#11/7/2010#)

But why would you use a real date like that? I think there is something I am missing.

[ctrapper]

Aytee.. let me try to clean up my explanation, and by the way, the date formula now works but I am not sure if it solved the issue.. so let's go this route.. I added a column in EXCEL and assigned a # sequence to the number of times the job ran. Example Job "ATL" ran 80 times over a given period, so I # each row where the job ran in sequential order 1-80. All I am wanting to do is easily obtain a rolling average, right now I am thinking after 5 occurrences. I should take the date of the equation because they don't run every day. So with that being said, I have a column where I want the Average to go, so in the Row that contains the 6th occurrence I want would like to populate the average of 1-5, then on the 7th occurrence I want the average for 2-6 and so on.. does this help? Here's my issue.. there are 30 jobs all listed in EXCEL, I can filter by Job but I cannot find a way to Average because the filter messes up the =subtotal(1, Range) I am using to Ave when using a filter. Someone suggested using SQL, but I don't have an SQL application, so they recommended ACCESS. If I am better of in EXCEL let me know. Thanks for all the help!

[ctrapper]

Good grief.. you can close this.. I apologize.. I didn't even think to do a sort in excel so all the jobs were grouped together where I could use the average formula then resort by sequence.. thanks for all the help!

[aytee111]

Excel is usually better at calculations and Access is better at storing and manipulating data.

Good luck!

[orange]

You might get some ideas from this if you still want to try Access
http://support.microsoft.com/kb/210138