Sum of two table rows from date to date

[rowbro]

Good day all. I am trying to figure out the best way to calculate the value of deposits and withdrawals in an access table between two dates. The dates will be dynamic based on user input, but I can't figure out an elegant way of doing the sum of all deposit values, less the withdrawal values, between the two dates. Can anyone advise?

Thanks in advance

 

[Joe4]

How is your table (tables) structured?
Are there separate columns for deposits/withdrawals, or is there just one amount column and a separate column to indicate deposit/withdrawal?

 

[rowbro]

Hi Joe - its two tables (one for deposits, one for withdrawals) each with a series of dates and either deposits or withdrawal amount (as adhoc events).

 

[Joe4]

Can you post the table/field names, and maybe some sample data?
Otherwise, we can just give you general advice, and leave it up to you to figure out, or create our own examples which may not mirror you structure and work the way that you want.

Note that if you just want to the SUM, you can use an Aggregate (Totals) Query.
To join together two tables with no linking fields, you would need to use a Union Query.

 

[rowbro]

ADVERTISEMENT

Table: T_Client_Info

ID	FirstName	SurName
1	Jo	Bloggs

<tbody>
</tbody>

Table: T_Deposits

ID	ClientID (from T_Client_Info)	DepositDate	DepositValue
1	1	3/4/2014	10,000
2	1	3/5/2014	8,000
3	1	1/6/2014	10,000
4	1	12/4/2014	5,000
5	1	1/4/2015	5,000

<tbody>
</tbody>

T_Withdrawals

ID	ClientID (from T_Client_Info)	WithdrawalDate	WithdrawalValue
1	1	1/5/2015	22,000
2	1	4/5/2015	8,000

<tbody>
</tbody>

Something like this. So basically, I would like to be able to calculated the value of deposits less withdrawals at any point in time for each client (in this case there is just one so far).

 

[Joe4]

Since your data is in two separate tables, I would first combine it into a single query using a Union Query (see this link for details on Union Queries: https://support.office.com/en-sg/ar...on-query-3856f16c-0a22-43f2-8c23-29ec44acbc05).

So the SQL code for that would look something like this:

SELECT ClientID, [DepositDate] AS TransactionDate, [DepositValue] AS TransactionValue
FROM T_Deposits
UNION
SELECT ClientID, [WithdrawalDate] AS TransactionDate, 0-[WithdrawalValue] AS TransactionValue
FROM T_Withdrawals;

If you paste this code into the SQL View window of the Query Builder and view the results, you should see all your transactions combined together with a single TransactionDate field and single TransactionAmount field (where the Withdrawals show up as negatives).

Now, you can create a new query from this query, where you can add Criteria to limit it by ClientID and Transaction Dates, and use an Aggregate Query to get the SUM you want (GROUP BY ClientID, Sum the TransactionAmount field, and only use the TransactionDate field in your Criteria (do not Show, use "WHERE" in Totals Row for this field).

So if we called our Union Query "CombineTransactions", the SQL code of this Aggregate Query may look something like this:

SELECT ClientID, Sum(TransactionValue) AS TotalBalance
FROM CombineTransactions
WHERE TransactionDate Between #1/1/2014# And #1/31/2015#
GROUP BY ClientID
HAVING CombineTransactions.ClientID=1;

 

[rowbro]

Thanks, will give that a try! Much appreciated.

 

[Micron]

I'd use 2 totals queries: one for deposits, one for withdrawals. Then a 3rd query based on those two with a calculated query field SomeName: sum([deposits]-[withdrawals]). Substitute your own table/field references of course. One less query if I counted correctly. Good for you that you did not ask about storing the calculated result in a table!