• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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