Percentage change

[Bernard Pullon]

I'm sure this gets asked over and over again (I did try to find an answer).

I have a table that has four fields, three of which are, when combined,
unique. The fourth field is an index.

I want to be able to get the user to specify how many months (one of the
fields) back they want to make the comparison and then for the query to
calculate a running percentage change using that range to make the
comparison.

The four fields are strProvince, intItem, and dtmPeriod, these make up the
unique fields, the fourth is simply sngIndex

I know there's some fancy way to do this, if you can help that would be so
helpful.

Thank you.

 

[Guest]

I could not follow some of your explainations.What does this mean?
From the field names of your table I do not see a field that appears to be a
datetime field. Which field has your dates?
Which field would be compared?

 

[Bernard Pullon]

Thank you for the reply, I patted myself on the back for making it clear but
I see I didn't do a good job ;-)

By 'index' it is a price index (CPI) and I want to be able to produce a
query that asks how many periods back should the comparison be made.

Eg. If dtmPeriod (which has values such as Mar-06, Apr-06 etc using a mmm-yy
format), if I wanted an annual (month on same month of previous year)
percentage change, then I want the query to calculate changes (eg. Aug-06 /
Aug-05 value of the index), in other words, 12 periods back.


The sngIndex field would be the one compared, ie a running percentage
change.

It would have to be grouped by strProvince, intItem and dtmPeriod

Thank you.