Simple Question- Date grouping

[Benjamin Bolduc]

Hi Everyone!

I'm having some trouble with a query I'm working on.

I have a query that shows the Sum([#cases]) produced grouped by Format(Date, "M").

This shows me the total number of cases produced for every month.

I need to know how to get the monthly average of the cases produced, but I cant figure out how to do it in a query.

Thanks for your help!
-Ben

 

[WebFaktor]

I am not certain, but I think. . .

I think you'll need to determine the following:
The number of months for which you are concerned, and then to divide this number by the total number of cases sold within that time period.

does this make sense?

 

[Benjamin Bolduc]

That does make sense, but I need to know how to do that in a query. I have thousands of production records, grouped in the query by month with the sum of cases produced for each month. But now I need a single average for each product. I hope this is making some sense. Thanks for your help!!

-Ben

 

[WebFaktor]

OK. . .

Are you using a crosstab query? If so, use this crosstab query within a select query and create the following:

Create a new Field for : Sum([#cases])
with Criteria: Between #1# And #12# 'or what ever time period you are after

If a crosstab query is not involved here - the above code and the concept behind it should still somehow fit into your query structure.

Let me know if I'm off track.

 

[WebFaktor]

One more thing. . .

then take the reult of the above query and build a new query expression that divides this result by whatever number you would like.

[#cases]/12

 

[Benjamin Bolduc]

Hey i figured it out! It wasn't exactly what you said WebFaktor, but you sparked the idea in my head that fixed it... so Thanks!

I love this forum