# Sum of two table rows from date to date

#### rowbro

##### New Member
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?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### 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

##### New Member
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

##### New Member

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:
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;``````

#### rowbro

##### New Member
Thanks, will give that a try! Much appreciated.

#### Micron

##### Well-known Member
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!

Replies
2
Views
532
Replies
5
Views
290
Replies
18
Views
487
Replies
5
Views
140
Replies
3
Views
273

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,987
Messages
5,834,720
Members
430,313
Latest member
smartykatwinks

### 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.

### Which adblocker are you using?

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

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