Sum of two table rows from date to date

rowbro

New Member
Joined
Dec 16, 2010
Messages
43
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 16, 2010
Messages
43
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 16, 2010
Messages
43

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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:
Code:
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;
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,564
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top