Having had some experience with ACCESS at work and volunteering to becom a treasurer for a Lions Club, I decided to use that DB to keep track of financial records. I inherited a bunch of excel files with multiple worksheets linked by various calculations hidden. No manual existed and "actions" were in the memory of several of the "old hands". So I decided that a "treasurer's manual" was needed to collect all the unwritten procedures anc conventions, That led to a need for a Work Breakdown Structure so I could code the actions and establish meaninful queries. I initially entered members accounts (55) in one table, and checks for the administrative segment and charity donations in another table.
Processing got overwhelmed quickly because I was told I must separate Administrative actions from Charitable contributions (IRS). The WBS got complicated because it seems like every action I made would result in someone telling me (nothing written down) of an exsiting "conventional method". I started with a numerical coding system but then I had to have a table and I encountered variations of "procedures" and had to add more numbers.
I had to split out the members accounts into 55 tables, then discovered the maximum tables access could link is 52.
Anyone out there with suggestions, pointers or any kind of help?
I had a code for each entry, as well as the actual data that the table became so wide that when I scrolled across I made incorrect entries. I sent out bills and, embarrassingly, had to make corrections.
Since there was no documentation of the rules and procedures, I would learn about something afterwards.
Obviously, I should have written the manual, had it vetted against the WBS before I set up. Time ran out before checks started coming in and bills had to go out.
I did not create forms or reports from either the consolidated member table or the individual member tables. (My excuse is time pressure.)
What I did do is append all the individual members tables into a consolidated table. Now I have to be certain a new record is identical in both.
I did some web searching to see if I could find examples of how someone might use Access for a non-profit treasurer's job, but wasn't successful with my results.
None of which explains why you felt any need to make 55 tables from 55 member accounts. If time pressure was the reason, and the reason to dodge building your database correctly, then there is not much anyone can do to help you besides recommend that since you don't have the time, perhaps you should get a local consultant / developer to do it for you... properly.
I will easily concede to the statement that I didn't build the database properly.
The 52 table excursion was a desperation move because I did not have the time to develop a report for an invoice.
This is volunteer work for a charitable organization, therefore professional help is not financially feasible.
Are there documentation sources for small scale financial management using databases?
Yes off-the-shelf software was considered, but everyone would have to buy licenses. I tested my personal copy of Quicken and found it reversed deposit and withdrawal information. Quicken support did not explain nor help correct the problem.
I did not create 52 new tables, just segregated each member's data into individual tables using the data for all members in a single table. The issue of segregation of accounts for tax reasons caused that. Obviously something that was missed in the design. I plan on reverting to one table for members as soon as I finish a WBS which can clearly identify administrative actions from charitable ones.
I started this task with no documentation on the process and recognized that must be done first before I created a data base. Soliciting information from long time members produced the majority of procedures, but not all.
Should I focus on the report (the bill to members format) and then restructure the single table so that it provides the right information on the bill?
For now you could UNION the tables to produce a query that you can link to - this effectively creates a single virtual table you can reference for your report. I would suggest it should be an interim measure.
Have you tried applying for grants? Charitable organisations (at least in my neck of the woods) can usually get their hands on money for this sort of thing. The last charity I did some work for negotiated my bill upwards because they needed to spend their entire grant to get a big payment next year.