Calculating Subsets of Data

[snowiii]

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

 

[kc-mass]

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

 

[Jeff Boyce]

"most recent 6 months" could mean the preceding six months, or it could
mean, as 'kc' points out, the cumulative total so far since 1/1/ of the
current year (or 7/1/, if after June).

What do you mean?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[snowiii]

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

 

[snowiii]

The objective is the preceding 6 months...When there are less than 6 months
of preceding data, calculate based upon whatever that number might be (e.g. -
1 month, 2 months, 3 months, 4 months, or 5 months of data)

 

[Jeff Boyce]

So if I have this straight, you want to find any records that are within the
past six months (even if that's only one month's records)?

Take a look at Access HELP re: the DateAdd() function. You can use this to
determine the date six months before ... ?today's date?

And if you need a way to get "today's date", look up the Date() function.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

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

How are your records stored, and how are the dates stored within them? If
there is a date field in the table, then a criterion of

= DateAdd("m", -6, Date())

will return only records within the past six months... but I'm not sure that
fits your data!

 

[kc-mass]

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