?
Solved

MA Access - Salary changes for consequtive dates

Posted on 2012-09-18
1
Medium Priority
?
377 Views
Last Modified: 2012-09-18
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
Comment
Question by:maroulator
1 Comment
 
LVL 40

Accepted Solution

by:
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)));

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question