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