• 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.

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;


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
If you need only one string for each employee, use this sample (queryResult)

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