Assuming that you are reporting the agencies seperately and assuming further
that you have a date field that i'll call datadate you could do it as
follows:

SELECT DateAdd("M",-6,[ToDataDate]) AS FromDataDate,
Max([tblyourtable][DataDate]) AS ToDataDate
FROM tblyourtable;

That will give you the latest date in your table for that agency and the
date six months prior to that date. use these in a "between" expression in
your final query. Some agencies will only have 30, 45, 67 days of data.
That's ok. It will only retrieve the data that is there from 1 day or six
months.

Regards

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...