• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

MS Access Question - Query Syntax

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 the most recent (see As_Of_Date) and the second most recent record for every individual listed. For example I need MS Access to compute the percentage change between Tom's salary on 3/11/2011 and 11/1/2010.

Any help would be appreciated.

Christos
Test-File.xlsx
0
maroulator
Asked:
maroulator
1 Solution
 
Rey Obrero (Capricorn1)Commented:
run this query



SELECT Test_File.Name, Test_File.Salary, Test_File.As_Of_Date, (select max([salary]) from Test_File as T where T.Name=Test_File.Name And T.As_Of_Date<Test_File.As_Of_Date) AS PreviousSalary, ([Salary]-[PreviousSalary])/[PreviousSalary]*100 AS PercentageChange
FROM Test_File
ORDER BY Test_File.Name, Test_File.As_Of_Date;


Result


Name      Salary      As_Of_Date      PreviousSalary      PercentageChange
Andrew      $60,000      10/5/2010            
Andrew      $65,000      4/10/2012      $60,000.00      8.33333333333333
Kevin      $60,000      4/9/2010            
Kevin       $68,000      10/5/2011      $60,000.00      13.3333333333333
Kevin      $70,000      5/20/2012      $68,000.00      2.94117647058824
Tom              $61,000      11/1/2010            
Tom              $62,000      3/11/2011      $61,000.00      1.63934426229508
0
 
als315Commented:
If you need only one string for each employee, use this sample (queryResult)
DB27850650.mdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now