A Microsoft Access Query to get net transaction totals by Fund and Fiscal Year

Microsoft Access: Sum and Subtract.sql

-------------------------------------------------------------------------------
-- A Microsoft Access Query to get net transaction totals by Fund and Fiscal Year.
--
-- The process:
---- Incoming: Sum all incoming amounts by Fund and Fiscal Year
---- Outgoing: Sum all outcoming amounts by Fund and Fiscal Year
---- Result: Incoming - Outgoing by Fund and Fiscal Year
--
-- Known to work in MS Access 2010
--
-- Issue:
---- Will only work if there is an incoming amount for each fund and for each
---- fiscal year (hence transactions #7).
--
-- SQL Fiddle: http://sqlfiddle.com/#!6/acead/5
-------------------------------------------------------------------------------
-- Set up tables:

CREATE TABLE transactions (
	[id] int,
	[Fiscal Year] int,
	[Direction] varchar(max),
	[Amount] float,
	[Fund] int
);

CREATE TABLE funds (
	[id] int,
	[Fund Name] varchar(max)
);

CREATE TABLE FiscalYears (
	[id] int,
	[FY Name] varchar(max)
);

INSERT INTO transactions ([id], [Fiscal Year], [Direction], [Amount], [Fund])
	VALUES (1, 1, 'Outgoing', 30.00, 1),
	(2, 1, 'Outgoing', 50.00, 1),
	(3, 1, 'Outgoing', 100.00, 2),
	(4, 1, 'Outgoing', 50.00, 3),
	(5, 1, 'Incoming', 200.00, 1),
	(6, 1, 'Incoming', 150.00, 2),
	(7, 1, 'Incoming', 0.00, 3),
	(8, 2, 'Outgoing', 20.00, 1),
	(9, 2, 'Incoming', 80.00, 1);

INSERT INTO funds ([id], [Fund Name])
	VALUES (1, 'Fund A'),
	(2, 'Fund B'),
	(3, 'Fund C');

INSERT INTO FiscalYears ([id], [FY Name])
	VALUES (1, 'FY 2016-2017'),
	(2, 'FY 2017-2018'),
	(3, 'FY 2019-2020');


-- Basic Select Statement:

SELECT transactions.Direction,
	SUM(transactions.Amount) AS Total,
	funds.[Fund Name]
	FROM transactions, funds
	WHERE transactions.Fund = funds.id
	GROUP BY funds.[Fund Name], transactions.Direction;

-- Output:

-- | Direction | Total | Fund Name | 
-- |-----------|-------|-----------| 
-- | Incoming  | 280   | Fund A    | 
-- | Incoming  | 150   | Fund B    | 
-- | Incoming  | 0     | Fund C    | 
-- | Outgoing  | 100   | Fund A    | 
-- | Outgoing  | 100   | Fund B    | 
-- | Outgoing  | 50    | Fund C    | 

-------------------------------------------------------------------------------
-- Our actual goal:
-- Net Transactions by Fiscal Year and Fund

 -- MSSql uses ISNULL, MS Access uses Nz, `Format` works in MS Access, not sure about MSSql
SELECT incoming.fy AS 'Fiscal Year',
	incoming.myfund AS 'Fund',
	Format(Nz(incoming.mytotal, 0) - Nz(outgoing.mytotal, 0), "Currency") AS 'Total'
FROM (
	SELECT FiscalYears.[FY Name] AS fy,
		funds.[Fund Name] AS myfund,
		SUM(transactions.Amount) AS mytotal
		FROM FiscalYears, funds, transactions
		WHERE transactions.Direction = 'Incoming'
			AND transactions.fund = funds.id
			AND transactions.[Fiscal Year] = FiscalYears.id
		GROUP BY FiscalYears.[FY Name], 
			funds.[Fund Name]
) AS incoming
LEFT JOIN (
	SELECT FiscalYears.[FY Name] AS fy,
		funds.[Fund Name] AS myfund,
		SUM(transactions.Amount) AS mytotal
		FROM FiscalYears, funds, transactions
		WHERE transactions.Direction = 'Outgoing'
			AND transactions.fund = funds.id
			AND transactions.[Fiscal Year] = FiscalYears.id
		GROUP BY FiscalYears.[FY Name],
			funds.[Fund Name]
) AS outgoing
ON incoming.myfund = outgoing.myfund
	AND incoming.fy = outgoing.fy
ORDER BY incoming.fy, incoming.myfund;

-- Output:
-- | Fiscal Year  | Fund   | Total | 
-- |--------------|--------|-------| 
-- | FY 2016-2017 | Fund A | 120   | 
-- | FY 2016-2017 | Fund B | 50    | 
-- | FY 2016-2017 | Fund C | -50   | 
-- | FY 2017-2018 | Fund A | 60    |