how do i calculate percentage of change between two or more field.

[rgalsal]

Hello,
I have a budget development database that I built in MS Access 2003. I need
to be able to calculate the percentage of change for each budget account code
from one year to the next. The formula is 2010-2011 proposed budget minus
2009-2010 actual budget, divided by 2009-2010 actual budget. How do I do this
in MS Access 2003 using expressiona builder or SQL or whatever?

 

[Jeff Boyce]

How depends on what ... and we don't know what data structure you're using
....

For example, how you would do this would be different if you used a separate
..mdb file for each year's data (NOTE: NOT RECOMMENDED!), than if you use a
single database with a date and/or year field.

Generic solution:

* add up amounts for one time period/condition
* add up amounts for the other time period/condition
* divide one by the other

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Access MPV

--
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.

 

[Daryl S]

rgalsal -

Post your table structure so we can help. Mostly we just need the table
name(s) and the pertinent fields. Those would be the fields that tell us the
budget account code, the amount, and which data (e.g. budget 2010-2011) we
are looking at, along with the key values to the tables.

 

[Steve]

If your tables are set up correctly, you ought to be able to display
2009-2010 actual budget and 2010-2011 proposed budget for each account code
in a query. So put the following expression in a blank field in a query:

PctChange[2010-2011ProposedBudget] -
[2009-2010ActualBudget])/[2009-2010ActualBudget]

Steve
(e-mail address removed)