Solved

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

Posted on 2016-08-03
5
38 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)
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 17

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 34

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 17

Expert Comment

by:xtermie
ID: 41777742
good options provided
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 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

16 Experts available now in Live!

Get 1:1 Help Now