MS Access Question - Query Syntax

Posted on 2012-09-01
Last Modified: 2012-09-08
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.

Question by:maroulator
    LVL 119

    Accepted Solution

    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
    LVL 39

    Expert Comment

    If you need only one string for each employee, use this sample (queryResult)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now