pigeonfizz pigeonfizz - 1 year ago 51
SQL Question

Access 2016 & SQL: Totaling two columns, then subtracting them

Say I have a MoneyIN and a MoneyOUT column. I wish to total these entire columns up so I have a sum of each, then I wish to subtract the total of the MoneyOUT column from the total of the MoneyIN column. I also want to display a DateOF column and possibly a description (I think I can do that by myself).

This would be the original database where I get my information from:

+-------------+------------------+---------+----------+-----------+
| Location ID | Location Address | Date Of | Money In | Money Out |
+-------------+------------------+---------+----------+-----------+
| 1 | blah | date | 10.00 | 0.00 |
| 2 | blah | date | 2,027.10 | 27.10 |
| 2 | blah | date | 0.00 | 2000.00 |
| 1 | blah | date | 0.00 | 10.00 |
| 3 | blah | date | 5000.00 | 0.00 |
+-------------+------------------+---------+----------+-----------+


I would like to be able to type in a location ID and then have results show up (in this example I type 2 for the location)

+---------+----------+-----------+------+
| Date Of | Money In | Money Out | |
+---------+----------+-----------+------+
| date | 2027.10 | 27.10 | |
| date | 0 | 2000 | |
| Total: | 2027.10 | 2027.10 | 0 |
+---------+----------+-----------+------+


I have tried other solutions (One of which was pointed out below), however, they don't show the sum of each entire column, they simply subtract MoneyOUT from MoneyIN for each row. As of now, I am trying to do this in a query, but if there is a better way, please elaborate.
I am extremely new to SQL and Access, so please make the explanation understandable for a beginner like me. Thanks so much!

This is a table referred to below.

+-------------+-------+----------+-----------+-----------+
| Location ID | Date | Money IN | Money Out | Total Sum |
+-------------+-------+----------+-----------+-----------+
| 1 | date | 300 | 200 | |
| 1 | date | 300 | 200 | |
| 1 | date | 300 | 200 | |
| 1 | total | 900 | 600 | 300 |
+-------------+-------+----------+-----------+-----------+

Answer Source

The following should give you what you want:

SELECT DateOf, MoneyIn, MoneyOut, '' AS TotalSum FROM YourTable
UNION
SELECT 'Total', SUM(MoneyIn) AS SumIn, SUM(MoneyOut) AS SumOut, 
SUM(MoneyIn - MoneyOut) AS TotalSum FROM YourTable

Edit:

You do not need to alter very much to achieve what you want. In order to get Access to prompt for a parameter when running a query, you give a name for the parameter in square brackets; Access will then pop-up a window prompting the user for this value. Also this parameter can be used more than once in the query, without Access prompting for it multiple times. So the following should work for you:

SELECT DateOf, MoneyIn, MoneyOut, '' AS TotalSum
FROM YourTable
WHERE LocationID=[Location ID]
UNION 
SELECT 'Total', SUM(MoneyIn) AS SumIn, SUM(MoneyOut) AS SumOut, 
SUM(MoneyIn - MoneyOut) AS TotalSum FROM YourTable
WHERE LocationID=[Location ID]; 

However, looking at your table design, I strongly encourage you to change it. You are including the address on every record. If you have three locations, but 100 records, then on average you are unnecessarily repeating each address more than 30 times. The "normal" way to avoid this would be to have a second table, Locations, which would have an ID and an Address field. You then remove address from YourTable, and in its place create a one-to-many relationship between the ID in Locations and the LocationID in YourTable.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download