Query to Calculate Running Totals Including Debit vs Credit Calculations

[kjv1611]

Before anyone says anything, I realize that I'm probably making this too complicated for what I'm doing.

I thought I'd start building my own personal finance database (I know I could use Quickbooks or Quicken or Mint or many many other programs, but I just want to do this, try it out, and also I like the idea of having full control of my data).

So, on with the scenario and question at hand:

My overall project:
I built a database with accounts for everything rather than having just different categories. I also have account types and account subtypes, as well as a field that specifies if the account can be "to" or "from"... in other words, whether money can go to or from it.

So, entering transactions is going great so far, and I can get pretty simple results, but because of the setup, it's been maybe a little difficult to get my brain wrapped around what seems like a simple idea in Excel, not so simple in Access.

What I'm doing is using a single transaction to basically act like double-entry accounting (I think)... or at least am trying to.

The item I'm having difficulty with at the moment (My Question at hand):
I want to build a running totals query that shows the individual transactions for an account, along with a running total to the right. In Excel, this is super-duper simple if you're tracking only one account of course. And I may have a solution, though somewhat complex for the desired result, I think... but I'm getting a weird character result on that. I did see a possible solution, but haven't tried it.

What I want to talk about here is whether there is a better solution than what I'm doing, so to get to that point, here are the details about the database which are relevant to this question:

Tables involved = tblTransactions, tblAccount, and tblAccountBalance

tblTransaction Structure:
[ul]
[li]TransactionID[/li]
[li]AcctFromID '--This links to the account table for pulling account details, primarily the name, for where the money came from[/li]
[li]AcctToID '--This links to the account table for pulling account details, primarly the name, for where the money went[/li]
[li]TransDate[/li]
[li]TransAmount[/li]
[li]Note[/li]
[li]VerifiedWithBank (checkbox)[/li]
[/ul]

tblAccount
[ul]
[li]AcctID[/li]
[li]AcctName[/li]
[li]AcctTypeID[/li]
[li]AcctSubTypeID[/li]
[li]CanBeFrom[/li]
[li]CanBeTo[/li]
[li]IsActive[/li]
[li]Maybe some other fields, I forget, but none are relevant to the question, if they are there[/li]
[/ul]

tblAccountBalance
[ul]
[li]AcctBalID[/li]
[li]AcctID[/li]
[li]Balance[/li]
[li]BalanceTypeID - If I included it, I forget at the moment... the idea is/was to cover a bal I own vs avail credit vs a loan bal (so balance I owe)[/li]
[li]DateEntered[/li]
[/ul]

So, right now, I have a transactions list that shows the most recent transactions in descending order by date, so it looks like this:
TransDate AcctFrom AcctTo TransAmt

What I would LIKE to see is something like this:
TransDate AcctFrom AcctTo TransAmt AcctFromBal AcctToBal
(maybe)... I think that would be good, but I would be fine with something like this:
TransDate AcctFrom AcctTo TransAmt CheckingBalance (since that's what I"m primarily interested in)

And I may have come close to a solution involving a few queries to get just the checkbook one working, but I can't help but thinking there is a better way. Here is the jest of what the queries are:

Q1:
SELECT TransID ,TransDate ,TransAmt AS Credit
FROM tblTransaction t JOIN tblAccount a ON t.AcctToID = a.AcctID
WHERE AcctID = 2

Q2:
SELECT TransID ,TransDate ,TransAmt AS Debit
FROM tblTransaction t JOIN tblAccount a ON t.AcctFromID = a.AcctID
WHERE AcctID = 2

Q3:
SELECT TransID ,TransDate ,Credit ,NULL AS Debit ,DSum(...) AS RunTot FROM Q1
UNION
SELECT TransID ,TransDate ,NULL AS Credit, Debit ,DSum(...) AS RunTot FROM Q2

The DSum Function that I just abbreviated is getting the sum up to date all transactions for that query... but now that I think of it, I don't think that's going to work exactly the way I want.

I have build a couple other "current balance" queries, basically, that seem to work, separately... but they are just getting one value per account, not running totals.

Here is what I really WANT to do with any such totals:
1. Get the most recent balance entered for the account from tblAcctBal
2. Add or Subtract all transactions since the last balance entry date, so that the running total reflects the most recently verified balance.

Does anyone have any suggestions or references I could look at that would help with this? When I looked at the ledger and checkbook examples from Microsoft online, I didn't really get all what I needed, I think. And it may very well be that I set the thing up too complicated. I like the idea though, of having everything in the database as "accounts". And I read that at least one of the finance programs does the same thing - captures everything as accounts rather than just categories. The program was an open-source program that was (from what it seems) one of the most popular open-source free finance programs.

Thanks in advance for any suggestions of any sort




"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Okay, I took care of the funny characters. Seems odd to me, but I apparently couldn't union a NULL in one query with a currency value in another query. So I changed the NULL to a nullstring (""), and the query worked just fine.

Now I need to figure out some further details.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57