Sum() with Conditions in context of Multi-Table Reports Query

I am quite new to MS Access reports and I have been having trouble in grasping the while reports concept.
I have been trying to understand their purpose, functionality and how to manipulate them.
In that quest, I have learned few things which I want to share with others (not specific to reports).

Sum() function:

Purpose: As the name suggests, it basically sums up rows of a column.
Specialty: The thing that is not apparent from the name is that it can sum numbers even in textual format.
Usage: Following is how you use it in a simple query.

Now you can give names to this summed value as well.

The sum can be used multiple times as well.


You can select simple columns along with sums just like a normal select statement.

You can add a WHERE clause as well.

Multi-Table Query:

Purpose: As the name suggest, a query which contains more than 1 table is a multi-table query. In many cases, we need to take values from more than 1 table at the same time and that too based on interdependent conditions. 

Specialty: Generally UINON etc.are used when it comes to running a query on multiple tables. But I have found a way which is more intuitive and easier to understand and remember.

Usage: Following is how you use it in a simple query. (This is equivalent to UNION ALL query)

Adding a simple WHERE clause

Lets make it a little bit more complex and show the real benefit of multi-table queries

The above query will return all the rows of Column1 of both tables when Column2 values of both match each other.

Now lets move to the topic for which I am writing this blog post.

Conditional Sum() in Multi-Table Query for an MS Access Report:

There are two ways to assign values to text boxes in reports

1. By binding them to a Query/Table

2. By writing expressions for each of the text box.

Of course, both can be used in conjunction. 

I wanted to make in "Income Statement" so I will use that as use case here.

Let me explain the basic structure of the database. There is on table named "Revenues" and other "Expenses". Each entry in either table has multiple columns including "Date" and "TotalAmount".

Lets build the desired query step-wise.

First simple one.

Then a bit complex

Now adding conditions that apply on both tables the same way.

Till here its pretty straightforward. Now we want to sum diferent categories of expenses separately (This is where I got stuck for 2 3 hours and Google couldn't really help me. This motivated me to write this post)

Here, lets assume we have 2 categories of expenses

1. Business

2. Personal

Now check the following query (I am removing the WHERE clause from the end so that its easier to understand. But that clause will work as it is with the following query as well)

Now, there another logically possible way to accomplish the above thing

But this doesn't work (at least in MS Access) since sub-queries cannot return more than 1 row so the sum has to be calculated within the sub-query and the sum is returned as one row.

DISTICT is used here outside because even though the sum is calculated based on the criteria, the sub-query (in this case for some reason) returns the same sum value for all the rows in the "Expenses" table.

Now that you have prepared the query, lets see how we would use it in the "Income Statement" report. Following are the quick steps.

1. Create a saved query.
2. Open it in SQL view.
3. Put the above SQL query in it and save it.
4. Then create a blank report and open it in "Design" view
5. Open its properties.
6. Goto to Data tab and from there select the above saved query as its "record source"
7. Then create a text-box. 
8. Go to its properties, then Data tab and select "TotalRevenues" as its "Control Source"
9. Follow the steps for expenses and you will get yourself an income statement report.

NOTE: I have intentionally not focused much on the steps of making a report because the idea of this post is not to teach you the basics of report making rather how queries can be formed and used in reports.

I hope this was helpful to you guys. Feel free to leave comments and suggestions.