13 month average calc (but only 12 months per record)

[bstboy]

Please see attached to help understand what I'm trying to do.

I'd like to calculate a 13 month average May - April, with the 13th month being the previous year's April (so essentially April to April). How can I add that previous month's April to the detail of the report. I've also attached the design view of the report if that helps.

Thanks

 

[Alansidman]

What is the record source for your report. Table or Query? What are the fields you are employing? Can't tell much from your picture except how you want it layout. Formulas, Query statements etc. would be really helpful. Or simply, why don't you upload your db to the forum so that we can see what you are attempting. Be sure to only have sample data. Also make sure to do a compact and repair first.

 

[bstboy]

I've attached a sample of my DB.

The report is off of a query right now.

 

[vbaInet]

Pretty much same question as your other thread. For current April:

April_Current: Sum(IIF(Format([[COLOR=Red]DateField[/COLOR]], "mmyyyy") = "04" & Year(Date())), [[COLOR=Red]AmountField[/COLOR]], Null))

For previous April:

April_Previous: Sum(IIF(Format([[COLOR=Red]DateField[/COLOR]], "mmyyyy") = "04" & Year(DateAdd("yyyy", -1, Date()))), [[COLOR=Red]AmountField[/COLOR]], Null))

This will go in a normal SELECT query with Total enabled, not a Crosstab query. With this method you don't have to keep changing your field names etc.
Amend only the red bits.

NB: There may be missing closing braces because I wrote it directly on here. Should be easy for you to fix

 

[bstboy]

Pretty much same question as your other thread. For current April:

April_Current: Sum(IIF(Format([[COLOR=Red]DateField[/COLOR]], "mmyyyy") = "04" & Year(Date())), [[COLOR=Red]AmountField[/COLOR]], Null))

For previous April:

April_Previous: Sum(IIF(Format([[COLOR=Red]DateField[/COLOR]], "mmyyyy") = "04" & Year(DateAdd("yyyy", -1, Date()))), [[COLOR=Red]AmountField[/COLOR]], Null))

This will go in a normal SELECT query with Total enabled, not a Crosstab query. With this method you don't have to keep changing your field names etc.
Amend only the red bits.

NB: There may be missing closing braces because I wrote it directly on here. Should be easy for you to fix

A little lost now. My limited knowledge says that I need the crosstab query to get the data in the format I need for my report (i.e. original data is in list format and I need time phased with months across the top for the report). Plus, I don't really have dates, it's broken out into it's pieces and number formatted. So I have Year as a field and Month (1 = May....12 = April...fiscal year) as a field, I don't have 04/2011 for instance). Now the data for the crosstab query is generated through a make table query that is in list format, I supposed I could make some kind of change there.

 

[Alansidman]

bstboy:
I have created a crosstab query manually in your db. I only did April 2011, May 2011 and April 2010. I also in order to test it, placed a value in April 2010. You should from my example be able to create a crosstab manually that incorporates the balance of the months.

It is attached.

 

[vbaInet]

I just had a second look at the images and I don't get what the OP is asking for. It said on the image, "Would like a Prior year's April field before May" which isn't clear at all because the Years are Row Headings. Unless you understood the requirement Alansidman

 

[bstboy]

bstboy:
I have created a crosstab query manually in your db. I only did April 2011, May 2011 and April 2010. I also in order to test it, placed a value in April 2010. You should from my example be able to create a crosstab manually that incorporates the balance of the months.

It is attached.

Thanks, I see what you did and I'm almost there. However I'd like the prior years april on all fiscal years, looks like the query you build is only for 2011. So for 2010, I'd want to see the 2009 prior April, for 2011 to see the 2010 April (which you did) and for 2012 to see the 2011 April. I'm trying to adjust your expressions to the best of my ability but just can't get it to work.

The only thing I can think of is to create a query for each fiscal year and append them all together in one table (not even sure this will work). Is there a way to do this in just one query though?

 

[Alansidman]

I wonder if you made a query for each year and then did a union query to join them all together. I have to run to an appointment and don't have time to play with it right now, but will check back later this PM if you haven't solved and attempt a Union on successive queries.

 

[Alansidman]

I created four aggregate queries and then created a union query to consolidate them. Queries are named 2009, 2010, 2011 and 2012 and then the Union Query. Open the Union Query. You will have to create the other months June--March, but you should be able to do it from what I have done.

Alan

 

[bstboy]

Thanks for the help. I did it the same, but different yesterday. I'm not familiar with union queries so I created 4 Make Table queries and then appended them into a master table. Looks like the end result is the same as your union query. Let me know if otherwise.

 

[Alansidman]

While what you did will work, it has created a static answer. If you add new data, you will need to rerun the make-table query and do an new append. If you use the Union Query then you will have a dynamic answer through 2012 and will only have to run the query. It will auto update through 2012.

To learn about Union queries, look at this video. It is explained in the second half of the video.

http://www.datapigtechnologies.com/flashfiles/Unionquery.html

Alan

 

[bstboy]

Awesome, that was easy enough, and it kills unneeded queries and tables.