Annualize Rolling data

[Zandra]

Hi,
This is a huge project i am working on and i am stuck this part. I want to perform some calculation in order to get the ANNUAL NUMBERS FOR EACH FAC_CODE. For isntance, the FAC_CODE for FA255 has submitted a data for all 200701, 200702, 200703, 200704 and these are a Quarterly data. So what i am trying to do is to insert and a new field and call it in this case CY07 and annualize the data by using the formula below. Please note, i was using this formula in Excel so you may want to adjust it in order to make it work in Access. One more clarification in the formula, when the formula says (sum(SE) this means that we need to sum the data in the SE field for all Quarters like 200701 + 200702 +200703+200704 per FAC_CODE because in the table there are are 2 FAC_CODES: MN268 and FA255. I want to apply the same formula if there are other like CY08, CY09 and etc. I hope i made some clarification. Pls. let me know if this is not clear. Pls. see attached. thanks

(sum(SE) / (+AVERAGE(MIX)*(SUM(DISCH) / ((SUM(GROSS_TTL) / SUM(TOTAL_OP_GROSS)))))))

 

[David Eagar]

something like this (qryMainSum) ??

 

[Zandra]

Thanks, it works greate

 

[David Eagar]

Your welcome

 

[David Eagar]

ps there is the usual 10% consultancy fee - I like the look of some of them numbers!

 

[Zandra]

Thanks David. There is another post i just posted today but so far no one seems to help. If you help me with this number then we can look at some of these numbers. many thanks. Here is the post link:
http://www.access-programmers.co.uk/forums/showthread.php?t=177796