Not semi annual...Each agency has anywhere between 1 to 16 months of stored
history. What I want to do is calulate the average activity for the most
recent 6 months. If Agency A has 12 months just use the most recent six
months...If Agency B has 3 months, then just use the three months activity in
the calculation...Currently I determine the months of activity by using the
datediff calculation between the date the agency became active and a date
certain...I need something more robust to do the calculation and maybe the
answer is a vb script?

I hope this is clearer..

"kc-mass" wrote:

So are you really looking for semi annual totals like Jan - June and July to
Dec?
Else how can six mo be 1 - 6 months

Kevin
"snowiii" wrote in message
...
I have data for records that can span between 1 to 16 months.. I want to
be
able to calculate the average activity for the most recent six months in a
query and be able to display that in a report.

I can create the calculation in the query for the entire duration of
months
for each record but am stuck on how to have it choose only the most recent
six months of data to do the calculation and understand that the six
months
may really only be between 1 to 6 months of data..

Any suggestions would be greatly appreciated..My skill level is somewhere
between meatball surgeon and kinda knowledgeable...


.