Crosstab Query - Calc Difference based on 1 field used as header

T

Terri

Hi,

I am creating a query the will calculate the difference between fiscal
years. Fiscal year is one field within my database, but obviously in a
crosstab query, if their are multiple fiscal years, it will create a header
for each fiscal year.

Depending on if there is just one, two, or more fiscal years, how do I
create a calculated field to represent the difference / change between fiscal
years? (This will all be dependent on the users preference that is driven by
an interface form that I have built.)

How can I build the formula to accomodate the following different types of
situations:

*If the user only selects just one fiscal year. (The formula should not
error out because it doesn't have a value to compare to in another fiscal
year.)

*If the user selects two fiscal years. (The formula should calculate the
difference between those two fiscal years.)

*If the user selects more than two fiscal years. (The formula should
calculate the difference between two sequential fiscal years. e.g. FY2003,
FY2004, FY2005 - should calculate between FY2003 & FY2004; FY2004 & FY2005.)

Thanks for your help!
 
Top