Solved

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

Posted on 2016-08-03
5
30 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
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points (awarded by participants)
Comment Utility
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 17

Assisted Solution

by:xtermie
xtermie earned 125 total points (awarded by participants)
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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)
Comment Utility
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 17

Expert Comment

by:xtermie
Comment Utility
good options provided
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now