How to mimic += in Access

Sorry the title is vague but I didn't know how to explain it better

I am working on a small budgeting solution based on Access for me & my family,
let's assume we have 2 accounts (one bank and one cash) & each has its own set of transactions

1) I know I could use split form with the "TOTALS" in Home -> Records to display the sum of each account however if i want to have the sum inside the table or even in a textbox it is not working

2) How can I consolidate all the accounts to find the total balance of my accounts in one place?

thank you
LVL 49
AkhaterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Use a query that sums all the transactions.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you referring to += in .NET?
0
AkhaterAuthor Commented:
@Pat I was able to build a query to sum one of each account but not one across all the accounts.... moreover I wasn't able to find a way to display the result of the query in a textbox (or whatever)

@Scott yes it is kind of an analogy the first thing that came to mind but not really accurate sorry about that
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
however if i want to have the sum inside the table or even in a textbox it is not working

you can try use Sum clause in your SQL, or you can try look for DSum function in case you need to display a sum total in a control.

MS Access: DSum Function
https://www.techonthenet.com/access/functions/domain/dsum.php

on a separate note, if you're familiar with Excel and that's fine for you, you can also do the budgeting there. I got one for myself as well.
0
Jeffrey CoachmanMIS LiasonCommented:
Then you will have to explain your design a bit more...
I am working on a small budgeting solution based on Access for me & my family,
let's assume we have 2 accounts (one bank and one cash) & each has its own set of transactions
...You will have to explain how your tables designed and related.


In any event, when you say "we have 2 accounts", I will presume that each family member has one bank account and one cash account, ...and you want the sum of all accounts, for all family members
In other words, if you have two people in your family, ...you want the sum of four accounts.


I will admit that I am not expert on banking database designs, but I was intrigued by this question.
And this is what I came up with, ...and it seems to get the correct result(s)
...Ultimately it ended up being a formula like this, to sum all the account totals:
=DSum("atAmt","tblScottSave")+DSum("atAmt","tblScottCash")+DSum("atAmt","tblPatSave")+DSum("atAmt","tblPatCash")

Attached is the DB, and even the spreadsheet I used to test the data.

Lets see what the other experts may contribute...
;-)

JeffCoachman

JeffCoachman
Access--eeq29067318--FamilyBankAcc.accdb
Book1.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AkhaterAuthor Commented:
Thanks Jeff you are awesome that's exactly what I was looking for :)
@Ryan thank you I was trying to use the "+" sign didn't know I needed to use the DSum

Cheers
0
AkhaterAuthor Commented:
Thank you guys for the help

@Jeff thanks for the extra mile and including some samples it is awesome
0
PatHartmanCommented:
didn't know I needed to use the DSum
You didn't.  Using domain functions in queries is quite inefficient.  A Union query would have been better now that I know your data is in multiple tables.  Then you can sum the union query.  Having separate tables by person is a seriously poor design.
0
Jeffrey CoachmanMIS LiasonCommented:
Pat,
My sample was working on the assumption that this needed to be very simple.

By that, I mean that each account was indeed it's own actual "Bank account"
...This would make entering the data into the separate tables easier to to, ...rather than have a properly normalized table, where an lay person person might become confused with having to enter all transaction from 4 separate bank statements in one table.
That is the take-away I got from reading the initial post...
(I pictured each family member entering their transactions in their own table.)

I will check with the OP.

Jeff
0
Jeffrey CoachmanMIS LiasonCommented:
Akhater

Consider what Pat Posted.
If my sample works for you,...great.

But as your skills in Access get better, ...you may wish to "Normalize" this design down to one table.
tblTransactions
tID
tDate
AccountNo
tAmt

Also my design was also a bit flawed, ...you may not need the relationships, ...and you can probably get along without the account number field in each table...
(I was just thinking about possibly displaying the data from multiple tables in a query.)

Jeff
0
AkhaterAuthor Commented:
Thank you for the follow-up guys, I ended up merging them in the same table with "Normalization" as you proposed
0
PatHartmanCommented:
You're welcome.  You will find the table easier to work with this way.  You can use criteria to select data from a single account when that is all you want to see.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.