A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating Subsets of Data



 
 
Thread Tools Display Modes
  #1  
Old March 17th, 2010, 02:55 PM posted to microsoft.public.access
snowiii
external usenet poster
 
Posts: 8
Default Calculating Subsets of Data

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...
  #2  
Old March 17th, 2010, 03:02 PM posted to microsoft.public.access
Kc-Mass
external usenet poster
 
Posts: 362
Default Calculating Subsets of Data

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



  #3  
Old March 17th, 2010, 04:08 PM posted to microsoft.public.access
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculating Subsets of Data

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



  #4  
Old March 17th, 2010, 04:32 PM posted to microsoft.public.access
snowiii
external usenet poster
 
Posts: 8
Default Calculating Subsets of Data

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



.

  #5  
Old March 17th, 2010, 04:57 PM posted to microsoft.public.access
snowiii
external usenet poster
 
Posts: 8
Default Calculating Subsets of Data

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" wrote:

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



.

  #6  
Old March 17th, 2010, 05:41 PM posted to microsoft.public.access
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculating Subsets of Data

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

"snowiii" wrote in message
...
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" wrote:

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



.



  #7  
Old March 17th, 2010, 11:19 PM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculating Subsets of Data

On Wed, 17 Mar 2010 06:55:01 -0700, snowiii
wrote:

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

John W. Vinson [MVP]
  #8  
Old March 18th, 2010, 03:00 AM posted to microsoft.public.access
Kc-Mass
external usenet poster
 
Posts: 362
Default Calculating Subsets of Data

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.