?
Solved

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

Posted on 2016-08-03
5
Medium Priority
?
61 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 41

Accepted Solution

by:
PatHartman earned 1000 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 500 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 41

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 500 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

601 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