Sum of two table rows from date to date

rowbro

Board Regular
Joined
Dec 16, 2010
Messages
50
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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;
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top