Solved

MA Access - Salary changes for consequtive dates

Posted on 2012-09-18
Medium Priority
377 Views
To whom it may concern,

My output from a query in MS Access can be found in the attached file. I am trying to perform an operation in MS Access which takes the attached output (in table form) and allows me to calculate the percentage change in salaries between consecutive dates for each individual listed (see As_Of_Date). For example I need MS Access to compute the percentage change for Kevin's salary between 4/9/2010 and 10/5/2011 and then again the percentage change in salary for Kevin between 10/5/2011 and  5/20/2012. For Tom it would be the percentage change in salary between 3/11/2011 and 11/1/2010.

Any help would be appreciated.

Christos
Test-File.xlsx
0
Question by:maroulator
1 Comment

LVL 40

Accepted Solution

als315 earned 2000 total points
ID: 38409417
``````SELECT qry.Name, qry.Level, qry.Salary, qry.As_Of_Date, qry_1.Salary, qry_1.As_Of_Date, ([qry].[Salary]-[qry_1].[Salary])/[qry_1].[Salary]*100 AS Perc
FROM qry INNER JOIN qry AS qry_1 ON qry.Name = qry_1.Name
WHERE (((qry_1.As_Of_Date) In (Select TOP 1 As_Of_Date FROM qry as q WHERE q.Name = qry.Name And q.As_Of_Date < qry.[As_Of_Date] Order By q.As_Of_Date DESC)));
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month16 days, 4 hours left to enroll