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
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
PatHartman
Use a query that sums all the transactions.
Are you referring to += in .NET?
ASKER
@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
@Scott yes it is kind of an analogy the first thing that came to mind but not really accurate sorry about that
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
@Ryan thank you I was trying to use the "+" sign didn't know I needed to use the DSum
Cheers
ASKER
Thank you guys for the help
@Jeff thanks for the extra mile and including some samples it is awesome
@Jeff thanks for the extra mile and including some samples it is awesome
didn't know I needed to use the DSumYou 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.
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
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
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
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
ASKER
Thank you for the follow-up guys, I ended up merging them in the same table with "Normalization" as you proposed
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.