![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
stoutfellowBuilding a database is fun.
I should qualify that: what I am doing is to professional work roughly as casting out nines is to what I do for a living. I'm just using out-of-the-box tools from Access; I'm not even doing any genuine coding - constructing macros, at most.
But anyway. I'm reconstructing my financial records database. I originally built it in 2005, if I recall correctly, using Access 2003. Since then, I've learned quite a bit more, especially from my work on my library database, and I can see that a lot of it could be handled more cleanly. As an example: one form in the old database displays an account - say, my checking account - giving the cleared balances from each month, listing the uncleared withdrawals and deposits since the last balancing and their net sum. When I get a balance statement from the bank, I go through the following steps:
1) Enter the new balance.
2) Go through the balance sheet to make sure every item has actually been entered, and I enter any that haven't.
3) Bring up the system calculator and note the discrepancy between a) the difference between the last and current balances and b) the net sum of the uncleared items.
4) Go through and check off (using a Yes/No "Cleared" field) the items on the balance sheet.
5) Refresh the page, so that the newly cleared items are removed.
6) Compare the net sum of the remaining uncleared items to the result on the system calculator. If they agree, fine; if not, I have to go to the relevant table (there's no form for this), unclear all the items I just cleared, and try to figure out what's wrong.
Obviously, this is unsatisfactory. If nothing else, having to use the system calculator is unesthetic, and the troubleshooting process if there's an error is unpleasant.
So, I've added a Y/N "Clearing" field to the "Items" table, and a second list to the balancing form. Checking off the uncleared items will change the "Clearing" field rather than the "Cleared" field, and add those items to the second list. The net sum of the items on the second list will be displayed, and - next to it - the difference between the last and the current balance. Once everything's in agreement, I click another button, which changes the "Cleared" field for all of the items on the second list. Much cleaner, and less in need of backtracking. (I should say that that's what I plan to put on the new balancing form. I haven't constructed it yet, but I'm pretty clear on what I'll have to do.)
There are a number of other improvements I'm planning on making, but that one is illustrative.
The process of working out what tables I need, with what fields, what queries to construct, what forms to devise, what macros to write - it's just fun. The fact that I've upgraded from Access 2003 to Access 2010 adds spice. Trying things out, one change at a time; seeing what works, figuring out why what doesn't doesn't and fixing it; thinking of new improvements to add - it's entertaining activity, and satisfying work.
With any luck, I'll have it at least bare-bones functional by the end of Thanksgiving break.
I should qualify that: what I am doing is to professional work roughly as casting out nines is to what I do for a living. I'm just using out-of-the-box tools from Access; I'm not even doing any genuine coding - constructing macros, at most.
But anyway. I'm reconstructing my financial records database. I originally built it in 2005, if I recall correctly, using Access 2003. Since then, I've learned quite a bit more, especially from my work on my library database, and I can see that a lot of it could be handled more cleanly. As an example: one form in the old database displays an account - say, my checking account - giving the cleared balances from each month, listing the uncleared withdrawals and deposits since the last balancing and their net sum. When I get a balance statement from the bank, I go through the following steps:
1) Enter the new balance.
2) Go through the balance sheet to make sure every item has actually been entered, and I enter any that haven't.
3) Bring up the system calculator and note the discrepancy between a) the difference between the last and current balances and b) the net sum of the uncleared items.
4) Go through and check off (using a Yes/No "Cleared" field) the items on the balance sheet.
5) Refresh the page, so that the newly cleared items are removed.
6) Compare the net sum of the remaining uncleared items to the result on the system calculator. If they agree, fine; if not, I have to go to the relevant table (there's no form for this), unclear all the items I just cleared, and try to figure out what's wrong.
Obviously, this is unsatisfactory. If nothing else, having to use the system calculator is unesthetic, and the troubleshooting process if there's an error is unpleasant.
So, I've added a Y/N "Clearing" field to the "Items" table, and a second list to the balancing form. Checking off the uncleared items will change the "Clearing" field rather than the "Cleared" field, and add those items to the second list. The net sum of the items on the second list will be displayed, and - next to it - the difference between the last and the current balance. Once everything's in agreement, I click another button, which changes the "Cleared" field for all of the items on the second list. Much cleaner, and less in need of backtracking. (I should say that that's what I plan to put on the new balancing form. I haven't constructed it yet, but I'm pretty clear on what I'll have to do.)
There are a number of other improvements I'm planning on making, but that one is illustrative.
The process of working out what tables I need, with what fields, what queries to construct, what forms to devise, what macros to write - it's just fun. The fact that I've upgraded from Access 2003 to Access 2010 adds spice. Trying things out, one change at a time; seeing what works, figuring out why what doesn't doesn't and fixing it; thinking of new improvements to add - it's entertaining activity, and satisfying work.
With any luck, I'll have it at least bare-bones functional by the end of Thanksgiving break.
