Calculating Quarterly Data from Monthly data

[chuckcou]

I need to create a field that is a calculation. I want to determine quarterly data from monthly sales data.

Current table has--

rec id Date MonthlySales

1 1/1/2011 20,000

2 2/1/2011 15,000

3 3/1/2011 8,000

4 4/1/2011 10,000

5 5/1/2011 7,000

6 6/1/2011 15,000

7 7/1/2011 8,000

Let say I want Q1 Sales for 2011--I want to create a calculation field that calculates 2011 Q1 results.

Example:

So, some how I need to look at records 1/1/2011, 2/1/2011, and 3/1/2011 and average those amounts.

ie. (20,000 + 15,000 + 8,000)/3 Therefore the result should be 14333.33

I figure I can do an avg, but how do I get records from 1/1/2011..3/31/2011? Also what if there is not data{maybe an if statement}?

Thanks for you help. As a newbee it is much appreciated

[bcooney]

This is called a sub-summary report.

Here's a link about creating a quarter calc, and here's a sub-summary tutorial.

[chuckcou]

hmm, does that mean it has to be used in a report? I was hoping to have it show up in next to a portal on body of a form. Any other ideas?