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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|