Results 1 to 3 of 3
  1. #1
    forbudt4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    10

    Audit Comparison Report Formula

    Click image for larger version. 

Name:	Audit Comparison Issue.PNG 
Views:	12 
Size:	180.8 KB 
ID:	43590
    First off, I've tried searching quite a few places and cannot seem to locate a question similar enough to get an answer. In the attached screen cap you can see my current report, which I feel pretty much explains itself on what I'm trying to accomplish. All the red arrows point to every location I have a formula entered to produce a result and the formulas will pretty much be the same, but with the respective field names changing. As you can see, my current formula actually works in this specific scenario because the Min and Max numbers happen to coincide with the order I did the audits in. However, had the audit number outcomes been reversed for the Q4 & Q3 audits, I would unfortunately get the same result with my current formula... but, the results would not be true since I would actually be losing the amount of assets and the values associated with it (i.e. a negative number).

    I'm trying to come up with a formula, if possible, that compares the most recent audit (in this case Q4-20), to the previous audit (Q3-20) and produces the correct results whether a positive or a negative number. I know this isn't logical from an access standpoint, which is why I'm here, but it would be something like this.

    = "The Q4-20 or Most Recent Audit"[Pre-Audit Count]-"The Q3-20 or Previous"[Pre-Audit Count]

    So, in my exact scenario (which is correct by chance) shows I "gained" 27 assets on the most recent audit compared to the last one. But, if the numbers happened to be reversed, it would need to show I "lost" (27) assets. I'm not sure if this is possible because I have seen that Access Reports cannot distinguish the order or records in the details? But, if it's of any use, my report is set to sort the audits from oldest to newest for the date completed; and, although (as in this case) there might be more than 2 audits populating the detail section, I only need it to compare the bottom two records that way it would still work if I wanted to compare two audits from a year ago by applying a date filter.

    I pretty much feel like I'm up a creek without code, but thought I would take a swing at it. Please let me know if clarifications are needed!



    Thank you,

    Sean

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you will need to create a subquery to get values from another record in order to make a comparison
    http://allenbrowne.com/subquery-01.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    forbudt4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    10
    Thank you, Micron... I will review the tutorial and give it a shot!

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

Similar Threads

  1. Report Grouping Comparison?
    By mhadden in forum Reports
    Replies: 1
    Last Post: 10-05-2015, 03:11 PM
  2. Replies: 3
    Last Post: 09-08-2014, 10:36 AM
  3. Time comparison based on date - Late Report
    By gryphin81 in forum Reports
    Replies: 8
    Last Post: 04-25-2014, 11:35 AM
  4. Replies: 17
    Last Post: 04-18-2014, 01:35 PM
  5. Replies: 2
    Last Post: 02-09-2013, 12:39 PM

Tags for this Thread

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