MA Access - Salary changes for consequtive dates

Posted on 2012-09-18
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.

Question by:maroulator
    1 Comment
    LVL 39

    Accepted Solution

    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


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    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