Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

Access query/report - calculate difference between two totals, that are grouped.

I have a query whereby there are values for printing, for example:

Business Unit: IT
June 2015 £150
June 2016 £50

Business Unit: Finance
June 2015 £200
June 2016 £100

In my report I want to show the difference (saving) between the two years for each business unit. See attached document for query screenshot and report, with details of grouping etc.
Doc2.docx
0
melinhomes
Asked:
melinhomes
  • 2
  • 2
3 Solutions
 
PatHartmanCommented:
The calculation should be done in the report.  Queries are set operations.  They are not linear so you can't refer to previous or next records.  Even doing this in the report could be problematic.  I would need to have a better understanding of what the report needs to look like and what the underlying data is since obviously you don't want to refer to specific years in the calculation.

Also:

1. LIKE should ONLY be used when you are searching on a partial string.  In your query, none of the criteria is using a wildcard so they are always complete strings.  Use the "=" operator instead.  It is more efficient.
2. Always give your controls meaningful names.  Leaving the default of "Combo45" just makes it hard for others and even for yourself to understand what is going on.
3. The hard coded reference to 2014 is probably incorrect.  You'll have to change it every year.  You may be able to simply calculate the value as current year -2 and use ">" as the relational operator:
Where TransYear > Year(Date()) -2.
4. Columns should not be named using the names of functions or properties or other reserved words.  Year and Month are the names of functions and you can end up with subtle errors if you are not extremely careful with them.  Also avoid embedded spaces and special characters other than the underscore.  The easiest way to avoid reserved words, aside from memorizing thousands of names is to make compound names such as TransYear and SaleDate.
0
 
xtermieCommented:
You should perform the calculations on the report.
To do the subtraction locate the grouping field footer (per business unit) in the report and insert a field where you can do the calculation as desired - you can use the expression builder and scroll until you find the Sums/Totals that Access does and use the correct calculated field names.
Assuming you have the two calculated sums for the two years, like in the example above, you should be able to easily do a calulcation like:
             [AccessTotalJune2016]-[AccessTotalJune2016]
or something similar.
0
 
PatHartmanCommented:
If there actually were fields named AccessTotalJune2016 and AccessTotalJune2015, that would be a seriously poor table design.  The data is more likely in the same column but in different rows.  That means that the on Format event would need to be used to save one value and use it for the calculation on the next row but without some knowledge of the report and table structure, I can't provide exact instructions.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Yes, ...I too am a bit confused at to the exact output you are stating:
Business Unit: IT
June 2015 £150
June 2016 £50

Business Unit: Finance
June 2015 £200
June 2016 £100
...is this the "Exact" visual output of the query?

Based on what you seem to be asking for, ...I tend to agree with xtermie, ...that this would be much easier if done in a Report.

It is also not known how the "difference"' should be expressed.
Fiance minus IT,
Or
IT minus Finance
...expressed as a Negative or Positive number

So post an example of the "exact" output you are expecting...

JeffCoachman
0
 
xtermieCommented:
good options provided
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now