Rolling 12 Month Report

I need to create a report that shows Rolling 12 month sums over a
range of months.

i.e.  the range jan07 - dec07 would need to generate rolling sums
jan07 = sum(jan07 - feb06)
feb07 = sum(feb07 - mar06)
dec07 = sum(dec07 - jan07)

eventually i would like these rolling sums in a graph.

I have created the VBA and the query which creates a table with all of
the data I need, but I cant figure out how to get these sums in the
report in an efficient manner.

any ideas?  I am comfortable with VBA.


3/4/2008 2:47:23 PM
access 16762 articles. 3 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 33

On Mar 4, 8:47=A0am, wrote:
> I need to create a report that shows Rolling 12 month sums over a
> range of months.
> i.e. =A0the range jan07 - dec07 would need to generate rolling sums
> where
> jan07 =3D sum(jan07 - feb06)
> feb07 =3D sum(feb07 - mar06)
> ...
> ...
> dec07 =3D sum(dec07 - jan07)
> eventually i would like these rolling sums in a graph.
> I have created the VBA and the query which creates a table with all of
> the data I need, but I cant figure out how to get these sums in the
> report in an efficient manner.
> any ideas? =A0I am comfortable with VBA.
> Thanks,
> Adam

How is your table organized?  Are jan07, feb07, etc... column headings
or are they data in a column that tells the date for the data in that

I would shy away from using them as column names, and I would probably
use a real date instead of text like "jan07".  Access has a lot of
function for dealing with dates that are real.

Let us know how you've set up the table, and we can provide an answer.

Chris M.
3/4/2008 8:12:41 PM
column header is Fiscal Period, and the format of the data is 200801,
200802, etc.

I can convert those to actual date formats if you'd like, I often use
the DateSerial function since these databases store dates in YYYYMMDD
integer formats....
3/4/2008 8:44:50 PM
to expand, another column header is PieceCount.  So i would want a
rolling 12 month sum on the pieces.  I was thinking about using a
cross tab to get my sums per fiscal period.  but then i think i will
be forced to use some VBA to get the correct rolling 12 month sums
that correspond to each fiscal period.
3/4/2008 8:49:50 PM
On Tue, 4 Mar 2008 12:49:50 -0800 (PST), wrote:

>to expand, another column header is PieceCount.  So i would want a
>rolling 12 month sum on the pieces.  I was thinking about using a
>cross tab to get my sums per fiscal period.  but then i think i will
>be forced to use some VBA to get the correct rolling 12 month sums
>that correspond to each fiscal period.

Nope... not needed. There's an option to include a row sum in the crosstab
query wizard. That should do the job for you; just use a criterion 

>= CLng(Format(DateAdd("m", -12, Date),"yyyymm")) AND <= CLng(Format(Date()), "yyyymm"))
             John W. Vinson [MVP]
3/5/2008 12:37:27 AM

Similar Artilces:

Report Format
Env: CRM 3, VS 2003, remote connect to CRM server. I've created a QUOTE in VS2003 and when viewed using PREVIEW from within the VS environment it produces a beautiful QUOTE (at least in my opinion). When printed, again from the VS environment, it is perfect. When I upload the RDL file to the CRM server, create an new REPORT using this RDL then produce a REPORT, the formatting is all over the place. When I inspect the source (in VS) there are no fields that extend past the defined size. It appers that the CRM report engine randomly adds CRLFs all thru the report ... ??? ...

Document map 05-12-10
The document map for one of my documents has stopped displaying headings from the beginning of the document and instead displays headings from a point within it. The headings that aren't displayed are correctly formatted as Level 1. And I've entered and exited Outline view, with, unfortunately, no benefit. What might the solution be please? Are the headings that are shown the document map in the same style? If so, try reapplying the style to the headings that are not showing up. If not, make sure that it is the paragraph outline level that you have set to 1 (& if it ...

Cannot delete report
Hi All, I have install the report manager sometime ago, it is working fine. Until recently we found that when i click the action menu, nothing is come out. I can't delete or rename the report. But I can add new report. Do anyone encouter this before? Thanks. -- Regards, Venedict ...

Log ON Report Manager
Install Report Manger in the Server CRM Satisfactorily, but I have problems when I do log on to Crystal APS, I am using an account of domain administrator and it does not allow me to accesar to the APS. APS User Name: CCSGR3000\crmadmin APS password: ********* APS Server:servcrm I need Help, Thanks. The problem is with the login. Use the following: APS User Name: administrator Password: (should be blank) APS server: localhost (if it's local) I did run into a problem that Report Manager couldn't be accessed from anywhere on the network. It was an IIS configuration problem. &q...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

After 12.2.4 update, cannot open some Word docs with Open passwords
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel After installing the 12.2.4 Office update, I am unable to open some Word files that I had password-protected with an Open File password. How can I open? Thanks Basically, "you can't". Sorry, the other passwords are crackable, but "Password to Open" is effectively the encryption key. The entire file is encrypted, and unless you have the correct password, you can't get it open. However, are you sure you are not attempting to open files that were password-=protected on the PC? ...

Display Report Header/Footer
Can somebody please give me the telephone number of the smartass MS VP who came up with the bright idea for the Ribbon so that I can call her and have her tell me how to display the Report Header & Footer in a report? Obviously, I must be an idiot because I can't figure out how to do it with this simplied, intuitive, help-ya out m***** fu***** ribbon. Oh stupid me, its under 'ARRANGE'. How idiotic of me to think that it'd under 'DESIGN' since I'm 'designing' a report as opposed to 'arrangeing' a report. "microsoft"...

Payables Trial Balance Report with Options
I am trying to run an A/P Trial Balance Report today that will give me the A/P balance at January 31, 2007. I keep getting the balance as as February 28, 2007. I've run the same report for A/R and am able to get the balance as at January 31, 2007. Currently using GP 9.0. With my previous employer who was also using GP 9.0, I sure that we were able to run an A/P Trial Balance report for a previous period. Does anyone have any suggestions as to what I my be missing or what may be set up incorrectly? -- JR JR, The only date that you can spcify on the Aged Trial Balance With Options, ...

Crystal Report not found on File Repository Server
I recently upgraded our MSCRM server to a new server. We don't typically use the internal CRM reports, we have some custom ones created with PowerTrack that we primarily use. The CEO tried today though to open one of the canned MSCRM reports and got this error message. I tried the others and I get it on all of them. CrystalReportViewer File frs://Input/a_063/021/000/5439/6ac34043c58b1112.rpt not found on File Repository Server. I checked the input folder in c:\program files\crystal ent 9\filestore\input and it is empty. I would have copied them from the old server, but it is ...

eXtender and Crystal Reports
I've created a Crystal Report to print eOne solutions eXtender fields. They store inaformation in 4 different tables: EXT00101 (String), EXT00102(Numbers), EXT00103(Date),EXT00104(Time).. I have created a View that unions all these tables - and convert them into a Varchar.. Once that is done, I've created a function called GetExtFieldValue where i pass the extender window id and field number and it returns the value for that string. This report was working fine and all of a sudden it became so slow that now it takes 7 minutes to get one record. If I delete all the data from EXT...

Calculate Subreport totals in a main report Group footer
Hi all Apologies if this has been answered before but I can’t find it. I have a main Report with a Group called “Product_Category” which lists a number of “Products” in the Detail I have a Subreport named “product_costs” which has a record for each date and Text Boxes named “materials” and “fuel” (there are more but I’ll keep it simple). The Subreport sums all costs and has Text Boxes named “summaterials” and “sumfuel” in the footer (all with a height of 0.1cm) The Subreport is embedded in the Detail of the Categories and linked by Product_id In the Detail of the Main Rep...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <> wrote in message > Hi, > I need a...

EDC Reports
Is there a way to gain access to the EDC report(s) in SO Manager without having to configure it for EDC? I need the accounting department and back office mangers to be able to look at and print these reports without them actually having to or needing to do any EDC processing. You could use the Tender Summary report (Reports | Misc | Tender Summary) to get credit card totals by type & register. Marc "Alex" <> wrote in message news:edLaU8DUHHA.2212@TK2MSFTNGP02.phx.gbl... > Is there a way to gain access to the EDC report(s) in SO Manager without >...

Access 2007 12-17-09
I am building a contact data base for my church. How do I get the phone field to automatically format like this (xxx) xxx-xxxx when the numbers are typed in the cell? -- Thank You In the form design view select the phone control and open the properties dialog. Goto the Data tab and use the Input Mask to define the format you wish to use. If you click on the ... to the right hand side on the property you will get another dialog where there are predefined input mask or you can define your own. For instance, I use !\(999") "000\-0000;0;_ to format my telepho...

Report Query in RMS
Is there somewhere that I could get some information as to how the reports in RMS are composed. When you look at the various reports they all seem fairly simple in there basic structure. Are the any examples of how these reports are composed. The //--- Columns ---// iformation seems to be the same in most reports, same as the //--- Title Rows ---//. Setting up the flitters and the TablesQueried I need to understand. I have the schema for 1.2. How do I better understand the following or what do I need to learn. I wish I was in college again and had all the time in the world. //--- Repo...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <> wrote in message news:...

Permissions after 12.1.2 update
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Question. Not sure, but I after running the 12.1.2 office 2008 updater, I noticed this when repairing permissions using disk utility Permissions differ on "Library", should be drwxrwxr-t , they are drwxrwxr-x . Now, I also noticed other disk utility repair permissions that take place after the weekly overnight sripts run. I am just trying to figure out if running the office 2008 updater 12.1.2 could generate the permissions fix reported above. I installed this on 2 exactly the same computers, and didn't g...

Exporting Daily Z-reports from MRS to Quickbooks
Hello all, Basicly im trying to figure out how to export the daily Z-reports from Microsoft Retail Systems 1.2 to Quickbooks (I believe 2005) in 3 steps or fewer. Ideally, there should be a way to do this without exporting the data to a file, but rather do it directly from MRS after posting the z-report. If anyone can help please let me know if you are using the US version of Quickbooks, the ability to post directly from RMS to QB is built in - you just need to set it up and assign your accounts. Ask your RMS reseller and Accountant/Bookkeeper for help - usually takes about 2 hours t...

Increasing Months
Hi Here is a simple question and I cannot find the answer. On a report I have a text box [Text301] and when display I see Fev-07 I would like to add something like 35 other text box so month would increase by 1 What could be the formula for [Text302] to display Mar-07 or Mar 2007. The formula should be issued from [Text301] Then [Text303] related to [Text302], [Text304] related to [Text303], ....... So the day I change [Text301] for let say Apr-08 [Text302] would give me May-08 [Text303} would be Jun-08, etc... I dont realy care with the format; it can be Mar 08 or Mar-08 or Mar-2...

win7; money plus deluxe; monthly report failures
When using Microsoft Windows 7 and Money Plus Deluxe v17.0.120.1415. I'm trying to prepare my monthly reports. I get the error, "Money requires Internet Explorer 6 to function properly. Please reinstall Internet Explorer 6 so these components can be added". Win7 has IE8. IE8 > IE6. Shouldn't this work? I searched for this error elsewhere and couldn't find it, or its resolution. Any tips/tricks would be great. thanks... P.S: I know Win7 is Beta and that with Beta's come issues like this... I have the same problem and I wish there was a fix for it. Does anyo...

Sum of each grouped item in a report
I have a form that logs time spent on auditing activities for the Internal Auditors in my company. Each time the auditor performs an audit related task, they enter a new record under their name. This means they could have many occurences of the same function. I then created a query/report that breaks down the different auditing activities for each internal auditor. Management has now asked for a report that gives a total for each activity for each internal auditor (example - John Smith: Audit Prep - 2.25, Follow-up - 3.75, Audit - 7.50). I want to figure out how to do this on a rep...

Payroll deduction reports
I can't seem to find a report option or a Smartlist that will provide information on detailed deduction amounts for employees. Specifically we are looking for a report that would list the 401K deduction and 401K match per employee for a date range or YTD - can't seem to find any appropriate fields in Smartlist either. ...

Money 2005 Flyer Report bug
Hi all, I just found a bug in the "Frequent Flyer Point or Miles" report. The sorting of "Total miles/points available" is incorrect. It does not sort by value but by characters. For example, it gives me a sorted list of: (693) 1,956 10,284 21,136 3,056 3,279 3,365 686 Will Microsoft fix that and give us an update? I just bought Money 2005 for not long and don't want to spend another three hundreds on 2006 now. John MS almost never issue patches during the life of the product/year. M2005 has been out since mid-2004 so is getting on for 18 months old. Similar...

how do I forecast a 15% increase over 7 months?
Excel 2003 I have a YTD actual productivity number and need to forecast what the productivity would need to be over each of the next 7 months to achieve a 15% full year increase. The result would need to include the Headcount reduction/increase for each month. Productivity = (transaction/headcount)*(YTD Sales Days/monthly Sales Days) I have each month's transactions, YTD Sales days and montly Sales Days. Is this possible? Do you mean to say have estimates of transactions, YTD sales days, and monthly sales days already projected for the next 7 months? If so, and if you're ...

Changing field settings on multiple columns in pivot report...How
You have brought up a pivot report but it defeaults to count for all columns. Can you change all to max with one operation? Thanks You can't manually change all the calculations to MAX with one step. You can change each field individually, by double-clicking on it, and changing the summary function. Or, you can use programming to change all the data fields. For example: Sub MaxAllData() 'changes data fields to MAX Dim pt As PivotTable Dim pf As PivotField For Each pt In ActiveSheet.PivotTables For Each pf In pt.DataFields pf.Function = xlMax Next pf Next...