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...
0
Utf
3/17/2010 1:55:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
813 Views

Similar Articles

[PageSpeed] 8

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" <snowiii@discussions.microsoft.com> wrote in message 
news:5F2A8DE1-C7E9-4BDB-9720-B8C39581622D@microsoft.com...
>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... 


0
kc
3/17/2010 2:02:30 PM
"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" <snowiii@discussions.microsoft.com> wrote in message 
news:5F2A8DE1-C7E9-4BDB-9720-B8C39581622D@microsoft.com...
>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... 


0
Jeff
3/17/2010 3:08:19 PM
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" <snowiii@discussions.microsoft.com> wrote in message 
> news:5F2A8DE1-C7E9-4BDB-9720-B8C39581622D@microsoft.com...
> >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... 
> 
> 
> .
> 
0
Utf
3/17/2010 3:32:02 PM
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" <snowiii@discussions.microsoft.com> wrote in message 
> news:5F2A8DE1-C7E9-4BDB-9720-B8C39581622D@microsoft.com...
> >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... 
> 
> 
> .
> 
0
Utf
3/17/2010 3:57:02 PM
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.

"snowiii" <snowiii@discussions.microsoft.com> wrote in message 
news:74F74201-77BA-45CD-A3E1-165A95EEFD51@microsoft.com...
> 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" <snowiii@discussions.microsoft.com> wrote in message
>> news:5F2A8DE1-C7E9-4BDB-9720-B8C39581622D@microsoft.com...
>> >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...
>>
>>
>> .
>> 


0
Jeff
3/17/2010 4:41:25 PM
On Wed, 17 Mar 2010 06:55:01 -0700, snowiii
<snowiii@discussions.microsoft.com> 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]
0
John
3/17/2010 10:19:00 PM
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" <snowiii@discussions.microsoft.com> wrote in message 
news:5F2A8DE1-C7E9-4BDB-9720-B8C39581622D@microsoft.com...
>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... 


0
kc
3/18/2010 2:00:30 AM
Reply:

Similar Artilces:

Font size in data validation drop down lists
I am using data validation lists to control user inputs to a spread sheet. How do I change the font size inside the drop down list? I have tried changing the font size on the sheet itself & changing the font size on the list page. I can't seem to find a property setting for this font size. The only way to make it appear larger is to zoom in on the page. In a data validation dropdown list, you can't change the font size. There are a couple of programming workarounds here: http://www.contextures.com/xlDataVal08.html#Font Ryan wrote: > I am using data validation lis...

Modify scale in X axis on a scatter plot from Pivot data
I need to increase the gap between my labels on the X axis in a scatter plot. They are too close and I can not read the labels unless i make them 4pt and zoom in to 200%. There are about 200 points on the X axis at .2 increments. I would like to be able to have labels for whole numbers only. I can not use a fixed set of values by generating a dummy X-axis since the range changes as I apply the different filters. Any ideas? thanks! Sandy Pivot charts don't let you change the spacing of the categories. They also don't let you make an XY (scatter) chart, so you probably have ...

Why wont Word open Excel data source in mailmerge?
I am following the Help step by step, but each time i try to open data source it wont. I have saved the correct version in Excel and have renamed it etc MOSH "won't" means what? Do you get an error message? You cannot browse to an Excel file and use it? You have no ODBC driver for Excel? If the latter, you may have to re-configure and add that driver. Go to Control Panel(Start>Settings>Control Panel) and double-click on Data Sources(ODBC). In User DSN what do you see for drivers? If no Excel, click on Add and find Excel on the list(not Excel do). Select and "...

Hard-code source data
Is there any method other than using Dynamic Names/OFFSET to force a chart to always use specific columns and rows, regardless of whether or not new ones are added. We have multiple spreadsheets that track monthly metrics. Each month a new column is added representing a new month of data. I always want to show columns B-M to refelct the past twelve months. Currently when I add a new column representing a new month,the charts dymanically change to show columns C-N and I have to change over 20 charts back to B-M. Thanks! Melanie Martin Melanie - The way to handle this is with dynami...

Pivot Table Data Source, point to cell?
Is there any way to point the data source block to a cell. I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table and changing from the wizard. Is there a way to do this? Example: Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536 Data source in pivot table points to cell A1 to get the range. please tell me there's a way to do this.... thanx.! hi, Jeremy ! > Is there any way to point the data source block to a cell. > I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table >...

Printing reports with no data
Actually the title of this question is a little misleading, sorry. This should be fairly simple. I run 5 different reports for 2 different shifts based on certain product catagories that were produced the previous day. Sometimes there is data for those reports and sometimes not. Instead of the report opening in preview, I would rather just have a MsgBox pop up that says there's nothing to report. Other than date and shift information, I'm pulling up the product SKU, description of the product, start and end time, qty and so on. please help! Thanks Bill R. -- John 3:16 "Fo...

data validation fonts differ in drop down box
I am using Excel 2003. I have a group of cells with a data validation "list". That list contains 3 options -- check mark, "X", and "box" -- in Webdings. When you click the drop down box to make a selection the options are in alpha-numeric characters (Arial font(?)) and not Webdings. So my "check mark" becomes an "a" and my "X" becomes an "r". How can I get the drop down box to display the same font as the one in the list? Both the drop down box cells and the "list" cells are formatted with the...

Macro required to copy -past data to first empty row
Hi Expert, I am in need of such a macro or code by which I can copy & paste data from a particular row to first empty row of given row range. Kindly help. Regards, Vikky Hi Vikky See http://www.rondebruin.nl/copy1.htm Or if you want to find the row number of the last cell with data in a range see http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vikky" <love.excel@gmail.com> wrote in message news:1174498666.568772.117750@y80g2000hsf.googlegroups.com... > Hi Expert, > > I am in need of such a macro or code by ...

Querying Data from Access
All, I am using the charting features in Excel 2007 to build various charts from data stored in Access. I want the user to be able to select the Region, Country, or Site that he wishes to see data for, along with a range of dates. I have queries in Access that will summarize the data the way I need it. What I would like to know, is how do I "paramaterize" the queries so that I can use the dates the user chooses, for example? Thanks, Steve Here is a good example from EXCEL VBA 2003. for I think what you're wanting Public Sub CallStoredProcedure() Const Co...

The toolbar at bottom of screen has the word calculate
My excel sheet is not updating other files when they are all opened. The word calculate continues to appear in the ready toolbar at the bottom of the screen. The calculation option in the tools option is set at automatic, iteration is ticked and at maximum iterations. When a number changes the sheet calculates this sheet but not the other opened files. This file is not linked to any other file (i.e it is not dependant on any data in any other file) Can anybody explain what is causing this problem "Calculate" means there's a circular reference. Go through your worksheets u...

Requesting data from the Microsoft Exchange Server
Requesting data from the Microsoft Exchange Server My OutlookXP is retrieving data from the Microsoft Exchange Server "Server". You can cancel the request or minimize this message to the Windows taskbar until Outlook closes the message automatically. Why is this happening? Thank you. Normally causes by a slow network or a busy Exchange server. The client is waiting for a response from the server and either it is busy with another task or the network is slow. When it connects and gets the data, the message goes away. It is just there to inform users it is actua...

Sorting specific data
X-No-Archive: yes I need help to sort the contents of a cell. Cell A1= 01201 I wish to sort the numbers in A1 into descending numerical order. The result should be =21100 What is the simplest formula that I can use to achieve this? I would like to learn how to do this. Thamks On 10 Jun 2005 03:56:44 -0700, virfir97@yahoo.com wrote: >X-No-Archive: yes > >I need help to sort the contents of a cell. >Cell A1= 01201 > >I wish to sort the numbers in A1 into descending numerical order. The >result should be =21100 > >What is the simplest formula that I can use to ac...

How Calculate How Many Toolbars Will Fit On a Frame "Row"
Hello, I have several toolbars that I'd like to display on as few "frame rows" as possible. Since the User may change display resolution I can't hardcode which toolbars should be displayed on a row and I therefore need to calculate how many will fit at runtime. To do this I get the size in screen units of the Frame that holds the toobars and then, for each toolbar, I get the size (again in screen units) of the toolbar and use that to calculate how much "space is remaining" on the row. If there's enough room left I add the toolbar to the row and if not I star...

Repeat data from previous records in a form
I am creating a a form for data entry and want to know if there is a way to repeat data in fields from the previous record (or default to previous record). For example, I user has 100 entries - I would like to set up the form so they do not have to enter their ID each time they enter a record. When you hold the ctrl key and click the apostrophe key ' access copies the contents of the same field from the previous record. This assumes that the new record immediately follows the old record so you may have to sort the source table and requery it after each time a new record is inserted...

Displaying hidden data on a chart
I have a number of data series in a table on sheet 1, and this data is plotted as an x-y scatter in a chart on sheet 2. I want to hide a column in the table, but when i do this the related data series disappears from the chart. Is there a way to hide a column whilst still having it plotted on the chart? At the moment I have just set the column width to 1 pixel, which is a bit messy. Thanks Hi, Yes, select the chart and then via the menu Toosl > Options > Chart. unchecked the Plot visible cells only. Cheers Andy Nick Terry wrote: > I have a number of data series in a tabl...

Outlook 2007 stops acquiring data from Exchange
A week ago I noticed that Outlook 2007 on an old desktop in a guest room no longer was downloading any data from the Exchange server at work. I have tried absolutely everything I can think of but nothing seems to change this. Meanwhile, three other computers at home (we have a site-license arrangement which allows us to use Office on our home computers) with Outlook 2007 are having no problems at all with my Outlook account. Here is what I have tried so far: 1. Deleting the .ost file. Outlook acquires a few thousand kilobytes of data and then simply stops. The last entry in event viewer is an...

Data migration strategy from custom MSSQL-based app to CRM 4.0
Hello, We have a client looking to transition to CRM 4.0 from their current home-grown application based on SQL Server 2005. I am curious to get suggestions from the group on how to approach this. Here is one approach I am considering: 1. Create denormalized views in SQL (picklist values included instead of PK/FK keys) 2. Script queries to these views that send query results to a CSV file 3. Create data mappings in DMF to perform mass import 4. Run import with fingers crossed One requirement to note is the client is not looking for a staged migration - everything must be brought over at...

Office 97 to 2003: Excel: enter data w/fixed 2 decimal. 2000. be.
When I enter number "2000." I get 20.00 - yes, set decimals at 2 but earlier excel would accept change if you entered decimal with number. this is slowing down a budget prep and I'd sure like some help - online and tutorials say "enter decimal" but this is not working. what am I doing wrong? Hi Confirmed, until v ersion XP you could finish off with a decimal separator. This behavior changed with Excel 2003, I haven't seen any documentation on the reasons. "Enter decimal" is like 2000.0 which is just as many keystrokes as 200000 but slower. If you d...

won't calculate far enough
Hello, I have a formula (see below) that will work for rows 2-1000, but once I change it to look for columns beyond 2000, it will not work anymore... Any suggestions? This works: =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000) This DOESN'T work: =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999) -- Nicki Taylor Both you formulas work for me. -- HTH Sandy sandymann2@mailinator.com Replace@mailinator.com with @tiscal...

copy data into comments?
How can you copy data from a cell to the comment of another cell? -- Richard Northern California Be Nice to Everyone Everyday I don't think you can do this in a comment, but I good be wrong(I hope) All you want to know about Comments you can find here http://www.contextures.com/xlcomments01.html -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Richard Miller" <miller49@ix.netcom.com> wrote in message news:bg615b$uvj$1@slb9.atl.mindspring.net... > How can you copy data from a cell to the comment of another cell? > > -- > Rich...

Money 2004 unable to import Quicken 2004 data
I have Quicken 2004 insalled and downloade the MS Money trial and trie to impotr my Quicken 2004 data but got message syaing htere were unknown transation ytpes and none of the data gets imported. Does Modey suppotr conversion from Q2004? I have the very same issue here. Money reports a successful transfer but the accounts have zero balances and no transactions. I also tried the export from Quicken and then an import to Money-- Same results. I'm hoping to get it ironed out as I've used Quicken for years but the 2004 edition sux. "Phil" <anonymous@discussions.micr...

Import Purchase Order data to HQ
We have some stores and we place purchase orders in HQ system for every store. Now purchase order data is in a excel file and We need import PO data from it in HQ system. How can I do it? Thanks. Jerry wrote: > We have some stores and we place purchase orders in HQ system for every > store. Now purchase order data is in a excel file and We need import PO data > from it in HQ system. How can I do it? > Thanks. or qsimport or custom-process (for complete automation-system) bye antonio hi,antonio Thanks for your reply. You said custom-process mean customize a process myself...

How to..use worksheet form and save data to an Excel list
I'm using Excel 2003. Is is possible to create a worksheet form where the user enters data, saves the data and the data is then saved to the last row in an Excel list? Once the data is saved by the end user, I would like the form to refresh so that the end user can continue entering additional data. Does anyone know of any documentation or examples I could review regarding the above as I have never done this before? TIA. Maybe just Data|Form would be sufficient. If that doesn't fit your needs, look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform...

Looking for an Excel front end for data entry
Hi, I am looking for a front end to Excel that allows someone who is not computer literate to still be able to enter data. Like a form I guess where the filed or column is shown and they just fill in the text, then go to the next col as opposed to doing it in a spreadsheet way. I have looked at the Excel Form option but I need something a little more than that. Thanks rock If Microsoft Access is an option, I would recommend using that instead. Access provides much more flexibility than Excel with building a data entry form. The data are entered in a form you create, and stored in a t...

Inventory Turnover Report Calculation Seems Incorrect...
We are running GP version 9.0 and I noticed the results from the inventory turnover report are incorrect. Regardless of what item I run the report for the results are including in the Qty sold YTD sales from Dec06. This is causing the turnover # to be incorrect also. Is this report including Dec06 sales in error because the Dec06 ending inventory #'s need to be used in the calculation of the 2007 turns? When did you do your inventory close at the end of 2006? It sounds like it might have been done early in December rather than at the end of the month. -- Richard L. Whaley Auth...