Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Procedure for finding percent change from the two most recent records

    Hi, I'm trying to find percent change between the two most recent records in my database.

    I currently have a table of companies and the amount of surplus they have along with the date recorded. I am currently able to select the most recent date and get that to display by using Top1, but I also want there to be a column in my query that displays the second most recent date with that surplus and then a column showing percent change.

    Is this possible?

    Thanks in advance.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    There are two parts to this question. The easy bit is finding the percent change and the harder bit is finding the previous surplus.

    I attach a demo that does all the work in a query, that uses another query.

    qryLastSurplus finds the most recent surplus date for each company.
    qryMostRecentSurpluses joins the first query back to the tables to first find the amount of that last surplus, then:
    • Uses DMax to find, for each company, the largest surplus date before the most recent one
    • Uses Dlookup to find the surplus at that date.
    • Calculates the difference between the two surpluses
    • Expresses that as a percent of the Previous Surplus.

    You may not need to use the format("mm/dd/yyyy") expressions, but they don't hurt. Here is Aust we need them as we normally use dd/mm/yyyy date formats.

    Using a Dmax and a Dlookup in a query could be quite slow if you have a lot of data.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    That Worked!

    Thank you! I had been trying subqueries to get it done, but this works perfectly.

    I was wondering if you knew how to get rid of the #Error that shows up in the cell though if you don't have 2 dates. For example, some companies only have 1 date or surplus reported, and I'd like to be able to get rid of that error

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Handling errors is usually more work than the first run through of these things. You need to consider what can go wrong at each step, and want you want to do about it.
    You need to use IIF statements to test for error conditions, then put in an alternative.

    =IIF(test,value if test is true, value if test is false)

    You currently have
    PreviousSurplus: DLookUp("[Surplusamount]","tblCompanysurpluses","([companyID]=" & [tblcompanies].[companyid] & ") and ([surplusDate]=#" & Format([PreviousDate],"mm/dd/yyyy") & "#)")

    This step assumes you have already found a "Previous Date". If there is not one What do you want the answer to be? 0 perhaps? (or just the most recent surplus?)

    PreviousSurplus: iif(not isnull([previousDate]),DLookUp("[Surplusamount]","tblCompanysurpluses","([companyID]=" & [tblcompanies].[companyid] & ") and ([surplusDate]=#" & Format([PreviousDate],"mm/dd/yyyy") & "#)"),0)

    You can't divide by 0 so that gives another error for the perent change.

    PerCentchange: [change]/[Previoussurplus]

    Perhaps: PerCentchange: iif([previoussurplus]>0,[change]/[Previoussurplus],0)
    It would be better to display "NA", but you can't have a text value in a numeric field so that does not work.
    Regards
    John



  5. The Following User Says Thank You to johnhutchison For This Useful Post:

    tinghting (2011-08-16)

  6. #5
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    It works like a charm! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •