Running sum in query 2

[integritycare]

Hello,
Just wanted to know how I can modify this query so that the running sum will show the sum total per date selected., instead of showing the total for the all the table data.

SELECT T1.BankR, T1.DateRcvd, T1.BrokerStaffID, T1.TDeposit, T1.TDebit, T1.ItemName, T1.ChqNo, T1.CHK, T1.AccountNo, T1.AcctName, (SELECT Sum(TDeposit)-Sum(TDebit) 
   FROM tblBankRegister
   WHERE tblBankRegister.BankR <= T1.BankR) AS Balance
FROM tblBankRegister AS T1
WHERE (((T1.DateRcvd) Between [forms]![frmBanking]![txtStartDate] And [forms]![frmBanking]![txtEndDate]))
ORDER BY T1.BankR;

Many thanks

Integrity

 

[dhookom]

Have you tried adding the DateRcvd in the criteria of the subquery? Something like:

SELECT T1.BankR, T1.DateRcvd, T1.BrokerStaffID, T1.TDeposit, T1.TDebit, T1.ItemName, T1.ChqNo, T1.CHK, T1.AccountNo, T1.AcctName, (SELECT Sum(TDeposit)-Sum(TDebit) 
   FROM tblBankRegister
   WHERE tblBankRegister.BankR <= T1.BankR AND tblBankRegister.DateRcvd = T1.DateRcvd) AS Balance
FROM tblBankRegister AS T1
WHERE (((T1.DateRcvd) Between [forms]![frmBanking]![txtStartDate] And [forms]![frmBanking]![txtEndDate]))
ORDER BY T1.BankR;

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Hi Dhookom

Many thanks for your answer.
I have set this up as per your code.
However I do get some weird results.

DateRcvd TDeposit Balance

1/07/2013 $239.50 $239.50
1/07/2013 $86.00 $325.50
2/07/2013 $1,237.50 $1,237.50
3/07/2013 $1,627.20 $1,627.20
3/07/2013 $86.13 $1,713.33
3/07/2013 $121.27 $1,834.60

The first two entries are ok, but it does not continue as such:

DateRcvd TDeposit Balance

1/07/2013 $239.50 $239.50
1/07/2013 $86.00 $325.50
2/07/2013 $1,237.50 $1,563.00
3/07/2013 $1,627.20 $3,190.20
3/07/2013 $86.13 $3,276.33
3/07/2013 $121.27 $3,397.60

Do you have any ideas on what I am able to do next?

Many thanks,

Integrity

 

[dhookom]

What's wrong with the data you posted? I copied them into Excel and they seem to be creating a running sum over each date.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this criteria in your subquery ?
WHERE BankR <= T1.BankR AND DateRcvd Between [forms]![frmBanking]![txtStartDate] And T1.DateRcvd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hi guys,

In answer to Duane's question the running sum does not sum correctly in the access query.

Below is the query:

SELECT T1.BankR, T1.DateRcvd, T1.BrokerStaffID, T1.TDeposit, T1.TDebit, T1.ItemName, T1.ChqNo, T1.CHK, T1.AccountNo, T1.AcctName, (SELECT Sum(TDeposit)-Sum(TDebit) 
   FROM tblBankRegister
   WHERE tblBankRegister.BankR <= T1.BankR AND tblBankRegister.DateRcvd = T1.DateRcvd) AS Balance
FROM tblBankRegister AS T1
WHERE (((T1.DateRcvd) Between [forms]![frmBanking]![txtStartDate] And [forms]![frmBanking]![txtEndDate]))
ORDER BY T1.BankR;

Here are the results of that query with the date selected(1/7/13 to 2/7/13)

BankR DateRcvd TDeposit Balance
1548 1/07/13 $239.50 $239.50
1549 1/07/13 $86.00 $325.50
1536 2/07/13 $1,237.50 $1,237.50

The Balance should be $1323.00 not $1237.50. Seems to be an error in the query.. the first two entries are correct as far as the running sum results. The error gets worse as the date range is changed.

PHV
Thank you for your suggestion.
On replacing the Where clause the results do not work at all. In fact cant select the date range. The results give rather erroneous values from the running sum..

Many thanks

Integrity

 

[dhookom]

Tell us how you get $1323.00.

Please provide expected results and why.

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Hi Duane,
Sorry for the typo. It should have read $1563.00

The total deposits are: $239.50 +$86.00 + $1237.50 = $1563.00

So I thought that the running balance would read as follows:
1. $ 239.50
2. $ 325.50
3. $1563.00

and so on for that date period. If I remove the date from the query all the deposits align with the running balance ok. It is just when i use a date selection.


Is this correct?

Integrity

 

[dhookom]

I thought "sum total per date" meant running sum per date while apparently it means over the date period. Try:

SELECT T1.BankR, T1.DateRcvd, T1.BrokerStaffID, T1.TDeposit, 
T1.TDebit, T1.ItemName, T1.ChqNo, T1.CHK, T1.AccountNo, T1.AcctName, 
(SELECT Sum(TDeposit)-Sum(TDebit) 
   FROM tblBankRegister
   WHERE tblBankRegister.BankR <= T1.BankR AND 
     tblBankRegister.DateRcvd >= [forms]![frmBanking]![txtStartDate])
   AS Balance
FROM tblBankRegister AS T1
WHERE T1.DateRcvd Between [forms]![frmBanking]![txtStartDate] And [forms]![frmBanking]![txtEndDate]
ORDER BY T1.BankR;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Integrity, if BankR is properly sequenced in tblBankRegister then my suggestion should work.
What is the exact SQL code you tried and which erroneous results did you get ?

My concern is that 1536 2/07/13 appears after 1549 1/07/13 despite your ORDER BY clause ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hi guys,

Again many thanks for your replies.

Duane's code works great.Its was what I was asked to do.
PHV when I used the SQL code from Duane's first reply and then substituted your where clause
and then inputted a date range the result was all the data for that year.

The PK key has been sorted in Ascending order . So that the first entries ie: 1/7/13 would appear as the first entry in the query. Somehow the date range does not appear to work...

However I would be interested to know why a date range was not able to be selected..

Many thanks for your time

Integrity

 

[integritycare]

Hi Guys,
The running sum works great, however there is an issue with what I want to do.

On the form there is a Check Box (CHK) with the data Type set to Yes/No. The purpose of this box is used via conditional formatting to just change the background after the deposit/debit has been checked against the bank balance.
The check box is notable to be checked at all. It just seems to be locked. The properties (Locked) on this check box is set to no.

If I remove the code for the running sum it works ok. How can I get this to work. The query that I have used is as Duane has written above.

Many thanks,

Integrity

 

[dhookom]

Any query with a Sum() will not have any updateable fields. You would need to replace the subquery with a dSum() expression.

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Hi Duane,
Many thanks for your reply.

Just one last favour.. could you help me with the code so that the Dsum expression incorporates date selection, as per your previous post.

Many thanks,

Integrity

 

[dhookom]

Integrity,
What have you tried regarding DSum()?

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Hi Duane,

Many thanks for your reply.

I have tried using Dsum with the following code;

SELECT tblBankRegister.BankR, tblBankRegister.DateRcvd, Format(DSum("[TDeposit]","[tblBankRegister]","[BankR]<=" & [BankR] & " "),"$0,000.00") AS Balance, tblBankRegister.InvoiceNumber, tblBankRegister.ItemName, tblBankRegister.AccountNo, tblBankRegister.AcctName, tblBankRegister.TDebit, tblBankRegister.ChqNo, tblBankRegister.BrokerStaffID, tblBankRegister.Item, tblBankRegister.TDeposit, tblBankRegister.CHK, tblBankRegister.CHK1, tblBankRegister.TaxCode, tblBankRegister.CardType, tblBankRegister.EntBy
FROM tblBankRegister
WHERE (((tblBankRegister.DateRcvd) Between [forms]![frmBanking]![txtStartDate] And [forms]![frmBanking]![txtEndDate]))
ORDER BY tblBankRegister.BankR;

While this works buy selecting a date range, I still have a question the balance column. The result are show as below.

BankR	DateRcvd	TDeposit	Balance
1529	4/07/2013	$546.89	        $1,125,708.12
1530	4/07/2013	$478.82	        $1,126,186.94
1532	4/07/2013	$2,129.74	$1,128,316.68
1534	4/07/2013	$645.18	        $1,128,961.86
1535	3/07/2013	$1,627.20	$1,130,589.06
1536	2/07/2013	$1,237.50	$1,131,826.56
1540	3/07/2013	$86.13	        $1,132,033.97
1542	3/07/2013	$121.27	        $1,132,155.24
1547	4/07/2013	$355.00	        $1,135,958.36
1548	1/07/2013	$239.50	        $1,136,197.86
1549	1/07/2013	$86.00	        $1,136,283.86

What I would like to know is this. Is it possible that when the date is selected the first Deposit would be the first balance amount instead of beginning with a high amount,taken from the whole table?

Many thanks,

Integrity

 

[PHV]

DSum("TDeposit","tblBankRegister","BankR<=" & BankR & " AND DateRcvd>=#" & [forms]![frmBanking]![txtStartDate] & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hello PHV,

Thank you for your reply. I have tried this code but its still not what I am looking for. Below i have included some results to try and explain what I was hoping to achieve.

BankR	TDeposit	Balance
1614	$265.94	        $265.94
1616	$265.94	        $531.88
1647	$376.00	        $907.88
1650	$1,041.08	$1948.96
1651	$3,376.30	$5325.26
1656	$5,538.29	$10863.55

So between dates selected each report would be like as above.

I had thought of using an append query to get the desired results, but didnt want to really go this way
Many thanks

Integrity

 

[PHV]

Seems like you're not in USA, so try this:
DSum("TDeposit","tblBankRegister","BankR<=" & BankR & " AND DateRcvd>=#" & Format([forms]![frmBanking]![txtStartDate],"yyyy-mm-dd") & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hi PHV,
Many thanks for your reply. As usual your code works fantastically.. I do appreciate all the help you geniuses give.
By the way Im from land down under... Australia

Integrity