Bank Statemant (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
Hi I'm currently building a bank statement with a balance as below


Amount | Balance
-£11.58 £16.13
£20.00 £27.71
-£2.29 £7.71
£10.00 £10.00


But I am getting a lot of funny results so must be approching it wrong I've added images if the query, dsum and a screenshot of the statment report.


Hope somebody can help


thanks mick
 

Attachments

  • 2019-03-20.png
    2019-03-20.png
    65.3 KB · Views: 88
  • 2019-03-20 (2).png
    2019-03-20 (2).png
    71.6 KB · Views: 78
  • 2019-03-20 (4).png
    2019-03-20 (4).png
    98.9 KB · Views: 81

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
Just noticed the format error I think It's working not just need to validate the statement
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:31
Joined
Sep 21, 2011
Messages
14,038
Last edited:

plog

Banishment Pending
Local time
Today, 05:31
Joined
May 11, 2011
Messages
11,611
You shouldn't have seperate fields for debits and credits. Put the transaction amount into one field.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:31
Joined
Sep 21, 2011
Messages
14,038
Found some code that I used to do the same thing, but for all clients.

Code:
SELECT Emails.ID, Emails.TransactionDate, Emails.CMS, Emails.Client, Emails.Amount, Emails.Balance, DSum("[Amount]","Emails","[CMS]=" & [CMS] & " AND ID < " & [ID]) AS PrevBal, [Amount]+nz([PrevBal],0) AS NewBal
FROM Emails
ORDER BY Emails.ID, Emails.TransactionDate, Emails.CMS;
 

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
I think you need the balance from the previous record +- the latest credit/debit value
Have a look at Allen Browne's 'get value from previous record' example based on meter readings & see if you can apply that
http://allenbrowne.com/subquery-01.html#AnotherRecord


Think thats whats happening now as got it sort of working but seems to play up in places but getting there I'll check out allenbrowne site thanks
 

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
Got it had to get the sorting right plus add in the beginning balance looks right to me
 

Attachments

  • 2019-03-20 (6).png
    2019-03-20 (6).png
    61.3 KB · Views: 220

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
You shouldn't have seperate fields for debits and credits. Put the transaction amount into one field.


plog this is a double entry home accounts system having just one field would'nt work
 

isladogs

MVP / VIP
Local time
Today, 10:31
Joined
Jan 14, 2017
Messages
18,186
Sorry but the balances look wrong to me.
The balances in each record are BEFORE that transaction has taken place.
They should be AFTER it i.e. taking that change into account
So row 2 balance should be £3.00 (debit) less than row 1
It seems to me that you are making payments but the balance is increasing
Or am I misunderstanding your +- signs
 

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
Your right lol Noticed the final balance didn't match the bank balance.
Thay are correct now as I was filtering out Tests of the UNPOSTED I've now added them back in and am happy with the result as it looks the same as what my banks statement looks like but if you see something please let me know thanks


 

Attachments

  • 2019-03-20 (8).png
    2019-03-20 (8).png
    57.2 KB · Views: 228

essaytee

Need a good one-liner.
Local time
Today, 21:31
Joined
Oct 20, 2008
Messages
512
plog this is a double entry home accounts system having just one field would'nt work


Actually, it does work and if you want to show the figures as debit and credits (even though one amount field in the table) in various forms/reports that can be done as well.
 

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
Actually, it does work and if you want to show the figures as debit and credits (even though one amount field in the table) in various forms/reports that can be done as well.


There's lots of ways of doing everything access We all have our preferences mine is this method and can't change it now as the projects only got a weeks work left if that.
 

essaytee

Need a good one-liner.
Local time
Today, 21:31
Joined
Oct 20, 2008
Messages
512
There's lots of ways of doing everything access We all have our preferences mine is this method and can't change it now as the projects only got a weeks work left if that.


Yes, I agree with your clarification (above) and understand your predicament with only weeks left.


I was commenting against your assertion that it couldn't be done because it's a double-entry accounting system.
 

isladogs

MVP / VIP
Local time
Today, 10:31
Joined
Jan 14, 2017
Messages
18,186
Hi again
You seemed to indicate I was right in post #11 but the new statement still has the same 'issues' if that's the correct word.
This is what I think it should be

Payments made should reduce the balance not increase it.
So if you paid out £3 on 17/03 then the balance on that row should be £3 less than the row above.
However that £3 doesn't show on the balance until the next row when the negative balance becomes £3 less negative.
Now I can only makes sense of that if negative balances mean you are in credit...but that isn't logical to me...and its still on the wrong record anyway.
 

Dreamweaver

Well-known member
Local time
Today, 10:31
Joined
Nov 28, 2005
Messages
2,466
Hi again
You seemed to indicate I was right in post #11 but the new statement still has the same 'issues' if that's the correct word.
This is what I think it should be

Payments made should reduce the balance not increase it.
So if you paid out £3 on 17/03 then the balance on that row should be £3 less than the row above.
However that £3 doesn't show on the balance until the next row when the negative balance becomes £3 less negative.
Now I can only makes sense of that if negative balances mean you are in credit...but that isn't logical to me...and its still on the wrong record anyway.


As that balance is a minus as the account is in overdraft all those amounts are increasing I have to be honest and say it did confuse the hell out of me, but got my head around it all in the end I am having it checked by my accountant but all the numbers seem to work out.
 

isladogs

MVP / VIP
Local time
Today, 10:31
Joined
Jan 14, 2017
Messages
18,186
One last try.
1. The balance should be negative when you are overdrawn so put a minus sign on that expression.
2. The balance shown on each row should be the balance after that transaction has occurred. Yours is before the transaction so the result isn't shown until the following record. That was why I referred you to the meter readings example
 

Users who are viewing this thread

Top Bottom