Results 1 to 4 of 4
  1. #1
    DeanMcK is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4

    Percentage increase / Decrease

    Hi All,

    I need to calculate the Percentage increase / decrease from Month to month.

    The example below is for March increase/decrease from February.

    If my understanding is correct to calculate the increase the formula should be:

    (March2020 - February2020)/February2020 e.g. (18 - 1)/1 = 17 (1700%)

    and for decrease

    (March2020 - February2020)/March2020 e.g (18 - 1)/18 = -17 (-1700%)

    Code:
    SELECT qry100_March2020.What, qry100_February2020.February2020, qry100_March2020.March2020, 
    
    
    IIf([March2020]-[February2020]<0 Or [March2020]-[February2020]>0, ([March2020]-[February2020])/[February2020], ([March2020]-[February2020])/[March2020]) AS PercChange
    
    
    FROM qry100_March2020 LEFT JOIN qry100_February2020 ON qry100_March2020.What = qry100_February2020.What
    ORDER BY ([March2020]-[February2020])/[February2020] DESC;
    Hope this makes sense.

    Any help appreciated.



    Cheers

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is your question? Is the output not what you expect?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    to calculate percentage increase where the two costs are last month = A and this month = B

    you want, essentially, the difference between A and B, as a percentage of A.

    this gives you: (B-A)/A x 100

    so your calculation is essentially correct.

    however, you don't need all that IIF(A-B>0 stuff because there is the entirely wonderful absolute function: Abs(A-B) which will always give you the absolute difference between A and B.

    therefore, your query can be re-written:

    Code:
    SELECT qry100_March2020.What, qry100_February2020.February2020, qry100_March2020.March2020, 
    
    (ABS([March2020]-[February2020])/[February2020])*100 AS PercChange
    
    FROM qry100_March2020 LEFT JOIN qry100_February2020 ON qry100_March2020.What = qry100_February2020.What
    ORDER BY ([March2020]-[February2020])/[February2020] DESC;

    good luck with your project,


    Cottonshirt

  4. #4
    DeanMcK is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    Thanks Cottonshirt,

    works a treat.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Increase by one
    By LOIZOS in forum Programming
    Replies: 1
    Last Post: 04-08-2016, 08:57 AM
  2. Replies: 6
    Last Post: 01-28-2014, 11:44 AM
  3. "Decrease Vertical" grayed out on form
    By kelann in forum Forms
    Replies: 7
    Last Post: 10-26-2012, 01:43 PM
  4. Increase number by 1
    By elstiv in forum Queries
    Replies: 2
    Last Post: 05-14-2011, 12:25 AM
  5. Increase by 1
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 08-23-2010, 10:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums