Percentage Change Between Records

[milkman2500]

Hi,

I'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand. I was hoping someone could help explain.

Here are two examples:

http://msgroups.net/microsoft.public.access/calculate-difference-in-rows/100924

http://www.access-programmers.co.uk/forums/showthread.php?t=205729

I believe I need to create a query based on another query (which I can do). I would need to create a column in the new query that calculates the percentage difference, but what's the formula/contents of that column?

Thanks.

 

[plog]

Mathematically, percent change (http://www.mathsisfun.com/numbers/percentage-change.html) is this:

100*([OldValue] - [NewValue])/[OldValue]

From a database perspective the real key is getting [OldValue]. Usually this will require a DMax(http://www.techonthenet.com/access/functions/domain/dmax.php) function.

If you can post some sample data, include table and field names along with expected results I can help you more specifically.

 

[milkman2500]

Thanks for the quick reply.

While I understand the formula for percent change mathematically and in access across columns, I don't know the formula across records/rows. Sounds like DMax may be the answer. I'll investigate and see if I can post an example database with data.

I'm using a total query with the following:

[q_Survey_Grade_Analysis]
[q_Survey_Grade_Analysis].[Grade]
[q_Survey_Grade_Analysis].[Base_Salary]

Where Grade is grouped and ordered by descension, Base_Salary is averaged.
I want the percent difference between each grade on the base_salary.

So the ideal query would look like:

Grade Base_Salary Percent_Difference
3 $50,000 25%
2 $40,000 33%
1 $30,000


Thanks.

 

[milkman2500]

I think the best approach would be through a combination of a Dlookup formula and a sequential ordering column in the query. The dlookup would then have a criteria that is row number -1.

Anyone know how to add a column in a query similar to a autonumber field in a table, where the numbers increase by 1 for each record?

 

[plog]

What you've proposed is essentially a running count. You can search the forum for that term, but its not really going to help with what you ultimately want. It involves a DCount (http://www.techonthenet.com/access/functions/domain/dcount.php) where you order your rows and determine how many of them occur before the record you are currently on. Which is essentially the same thing I proprosed with the DMax.

You're trying to make ordering your data easier by proposing this, but to do this, you have to order your data.

 

[milkman2500]

Alright I figured it out. I had to create another query and pull in the first query as the source data, then use a combination of Dlookup and DMin.

DLookUp("Base_Salary","q_survey_grade_analysis","AvgofSort_Order = " & DMin("avgofsort_order","q_survey_grade_analysis","AvgofSort_order > " & [AvgOfSort_Order]))

Although it adds a lot of extra load time to the query....if anyone has a more efficient way, please let me know.