Calculate bank account balance

[dougdrex]

I'm using Access 2010 to create a personal budget database that will track balances for several bank accounts and assist with the family budget. More than anything else, I'm using this is a learning experience after taking a class in Access to reinforce the learning I gained.

What I'm trying to do is have the query return a single number which represents the bank balance. I'd like to create a separate query for each bank balance. I have inserted a picture of the tables I believe should be used for calculating the balances. Can someone tell me if I can derive the balances from the information I've selected?



Also, I believe the query will have to be written with SQL code as opposed to using the Query Design function. Does anyone have any ideas on how that query can be written to derive the bank balances?

[June7]

Build an aggregate query that sums the debits. Build an aggregate query that sums the credits. Build another query that joins the two aggregate queries to a master table of accounts and calculate the difference of the two sums.

The join you show in that image is not correct. The REF_BankAccounts table would have to be included in the query twice. Join one to FromAccount and one to ToAccount.

[dougdrex]

June7, thanks for the info, and I'll give those queries my best try.

Quick question for the benefit of my understanding...What is the difference between joining the REF_BankAccounts table twice the way you describe and the way I joined the single table to each field (FromAccount and ToAccount)?

[June7]

You should try each query and see what happens. The demonstration will probably explain better than I can.

[dougdrex]

June7, I had already began experimenting by the time I read your response. It took me a little time to figure out, but I did it!!

In case anyone in the future would benefit, here is the SQL code for the query I ended up with that aggregated all the debits from a specific account:

SELECT Sum([TBL_TransactionDetails].[TransferAmount]+[TBL_TransactionDetails].[ExpenseAmount]) AS [Reg Checking 50 Debits]FROM REF_BankAccounts INNER JOIN TBL_TransactionDetails ON REF_BankAccounts.BankAccountID = TBL_TransactionDetails.FromAccount
HAVING (((TBL_TransactionDetails.[FromAccount])=1));

[dougdrex]

Thank you VERY, VERY much June7

[June7]

Did you try a SELECT that joined one REF_BankAccounts table to both fields (RIGHT JOIN, not INNER)? Did that clarify?

[dougdrex]

I removed one of the REF_BankAccounts tables because I didn't need it to calculate only the debits. My understanding is that I should run separate queries for debits and credits. Then do a 3rd query to calculate the acct balance.

I'm not familiar enough with SQL to know the difference between a RIGHT JOIN and an INNER JOIN. I used the Query Design function to put everything together until it provided the correct results. Then I copied the SQL code into the thread so than anyone who reads it with a similar situation could benefit.

If I have misunderstood any part of your suggestion, please let me know. I am still in the learning process and want to understand as much as I can.

Thanks!
Doug

[June7]

Yes, the multiple queries is appropriate.

I was just trying to respond to your question in post 3. If you need a better understanding then the suggested test query should demonstrated why the displayed relationship will not work.

You know how to change the join properties in query designer? Switch to SQL view to see the constructed statement. You should see RIGHT, LEFT, or INNER.