Solved

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

Posted on 2016-08-03
5
55 Views
Last Modified: 2016-08-31
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
Comment
Question by:melinhomes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 37

Accepted Solution

by:
PatHartman earned 250 total points (awarded by participants)
ID: 41740712
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
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 125 total points (awarded by participants)
ID: 41742168
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41742521
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points (awarded by participants)
ID: 41751011
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
 
LVL 18

Expert Comment

by:xtermie
ID: 41777742
good options provided
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question