Bank Deposit Table(s) setup

pieniaszek

New member
Local time
Yesterday, 19:48
Joined
Dec 30, 2008
Messages
8
In managing bank transactions for a small charities group, I initially had a spread sheet which I imported into ACCESS and it has become difficult to track and relate deposit and withdrawal information. I decided to start from the design state, using ACCESS textbooks. I have to keep administrative transactions and charities transactions separate.
The "bank deposit" design has this problem:
If I deposit 5 checks (I scan each first) on one deposit slip, the bank statement will only report the sum. The deposit slip has several unique codes which I can use (collectively) to relate each specific check to that 5 check deposit. I have to distinguish between an 'administrative deposit' and a 'charities deposit'. I need the check number, amount and date from the administrative deposit as input to the members accounts, and need the check number, amount and date from the charities account as input to the unique list of charities.

The combination of bank identification number, date, deposit slip sequence number, and amount should be sufficient to make a single deposit 'non-repeatable', but I don't know how to make that number of variables as Keys.

Any suggestions?
 
This is definitely possible to do this with Access. I have built full accounting systems that include full bank account management. From printing checks, deposit slips and account reconciliation.

I have also done pledging/donation/gifting tracking systems in Access.

Do you have much accounting background?

How easy this will be to do will depend a lot on your table structures. Is it possible for you to supply us with your table structures?
 
Thank you for your interest in helping!
I do not have any accounting training. I am an engineer who has used ACCESS for other purposes and when I inherited this job, I was given many excel spreadsheets with multiple worksheets having inter-related computations, no written guidance, and nothing but unwritten "guidance". I started a manual, created a WBS, then built tables and queries. I did not start with a DB Design text, but now have two. Reconstruction is a challenge, but I can get by with what I have.

Attached is an excel file (DataViewDataSheet entries 090101). I had to create that because there is no way to download datasheets to check for consistency amongst tables. I did not include my WBS for Adminsitrative or Charities Accounts.

I appreciate your interest. I almost gave up entering any forum after some very critical comments (no help) on another forums.

John
 

Attachments

Attached is an excel file (DataViewDataSheet entries 090101). I had to create that because there is no way to download datasheets to check for consistency amongst tables.
I am not sure what this means. :shrug:

Does the spreadsheet represent your current table structure?
 
The spread sheet I sent are the Design Views of the 4 Tables I am using.

I guess the :shrug: term means that you don't consider them to be "structured"?

I am using what I have now, but I have a goal of restructuring the database by following text books.
 
the :shrug: was a habit form another forum where I post where it places a smiley for "confused".

I had to create that because there is no way to download datasheets to check for consistency amongst tables.
I was trying to say was the above statement is confusing. I am not sure what your are trying to say. ?????

Unfortunately text books and other resources that teach about data normalization principles will only teach you the theory. They do not show you how to actually do it for your exact situation. It takes years of experience building and redesigning/correcting databases to learn to do this properly.

You are going to need a very good understanding of accounting principles/practices and good business process management. By using this knowledge and data normalization principles, you will be able to properly design your database.

I took a look at your tables and there are some big issues. For instance, one table needs to be split into a parent /child table with a 1 to many relationship. You have a missing junction table. Two table need to be combined. One table has repeating fields that should be in a child table.

What would really help is if you could post a database with your tables. I would recommend making a new blank database and import the tables selecting the option for structure only unless it is only sample data. If no sample data, then enter a few record of sample data into all the tables. Compact and ZIP the database and attach it to a reply. This will allow me to easily make some design changes for you to check out.
 
My statement:"I had to create that because there is no way to download datasheets to check for consistency amongst tables.", was in reference to the Table Design View information. I wanted to capture that information in a spreadsheet file so that I could check Field Names, Data Types and Descriptions for consistency. I know I can print the complete information via Tools->Analyze->Documenter, and have, but had to create a spreadsheet.

I'm 72 years old and have been in the aerospace business for close to 50 years, so am well aware of the fact that theory is 'enlightenment' of a subject and it takes years of application for success. I still work and use the comparable words you expressed to me. Written documentation does provide a frame of reference which does prevent divergence of effort.

The fact that I should have never volunteered to become treasurer of this group because I am not an accountant nor have any experience in database design is history.
I was ridiculed on the dbForums already, so I know not to use that source.

I can create a database with sample data in each table.
I appreciate your interest in helping me.

John
 
John,

I downlaoded your spreadsheet and... dont take this the wrong way and with all due respect for your hard work... but you need to change some things NOW, that you are still designing this.

1)
It is good practice to NOT use any special characters in any column names, this includes spaces )(*&^%$/\ etc...

Also concistancy... HillVisit vs Hill Paid,
Check Num vs ChkAmt
atbl__ALC Members 081026 <== Note the double _
This is one of the reasons why it is better to just not use these.

2)
Repeating columns
ie. 9/10/2008 Mtg, 09/10/2008 Paid, 9/29/2008 Mtg etc...
This should be structured in a seperate table that has a 1:many relationship.
In which you then store: ID, Type (mtg/Paid), date

Same for Email1, Email2, Email3 and Position(s) (past or present), what happens if you want more positions/emails?? better to make it relational, instead of a fixed number like this.

3)
When building a database, it is usually not a good thing to include dates in your table names. You will be using these tables for more dates, hence the dates are meaningless/confusing.

4)
Dont worry about making/needing documentation (hardcopy or soft) to help you in structuring the data/design. This is actually a good thing, trying to get and keep thngs consistant and all will help you in the future.

5)
You can only have 1 key, in general it is a good idea to have a meaningless number be the Primary key, the ID (Key-Autonumber) field. Dont worry about other Keys
 
Very Helpful.
1. Consistency will be my first priority. As far as a date in the table names, I have already discovered the error because I have scripts keyed to tables with different dates and see the problem.
2. Repeating columns.
The problem of meeting attendance and charges (or not) has plagued me from the beginning. The unwritten rules of the organization are that I should bill people for future meetings. If the member is absent without an excuse, they pay. But, if a member pays for a future meeting, then is excused, I can't charge him/her, but in fact must give them credit. The members quarterly bill has to include not only their costs for meals, but their dues to three different organizations. Monthly, Im expected to report on income from meals and income from dues. Somehow, I need to differentiate dues and meals.
3.Table name dates.
My second priority will be to purge dates from tables. I will have to open 55 queries and change the table link. Since I have built a form for members bills, I may not need all those queries
4. Documentation
I understand your theme. The problem is that it seems that every board meeting "the cadre" tell me about another aspect of operation that I didn't know about. Nothing is written down, si I must do it to remember.
5. 1 Key
I generally use auto-number as a primary key, but see where I am running into problems with my transaction tables. I need to reassess the "one-to-many" and "many-to-many"relationships.
 
Written documentation does provide a frame of reference which does prevent divergence of effort.

I would definitely write every thing down.

Every successful organization I work with has written procedures (documentation) that they follow. IHMO, the lack of documentation/users manuals/help systems/written procedures is one of the biggest issues with productivity.

The problem is that it seems that every board meeting "the cadre" tell me about another aspect of operation that I didn't know about.

I feel your pain. I deal with "I forgot to tell you ..." on a daily basis. That is why I ask lots of questions and make lots of notes.


A well designed accounting system (database) should be able to handle most future needs without any or minimal database design changes. That is one of the ways that the "rules of data normalization" help you, if followed.


How is your sample database coming?
 
Last edited:
Working on it.
Due to the tables containing personal information and I do not have permission from individual members to release the information publicly, I am constructing pseudonames and psedo id's. This will take time.
 
The members quarterly bill
This may prompt you to make 3 month records, but really just one record per billable item is the thing to do on any billing system. Debit or credit, just make seperate entries into your invoice table and you will get where you want to go.

I will have to open 55 queries and change the table link.
No you wont, if you did things 'right' in most places access will automagicaly change the table names for you if you rename a source table.

I generally use auto-number as a primary key, but see where I am running into problems with my transaction tables. I need to reassess the "one-to-many" and "many-to-many"relationships.

No such thing as a Many-to-many (M to N) relationships!!!! NEVER :eek:

My point by the way was exactly the oposite of what you are understanding, it is a good thing to have a one column, meaningless automatic key to most every table. I wish more people would do so ;)
 
Working on it.
Due to the tables containing personal information and I do not have permission from individual members to release the information publicly, I am constructing pseudonames and psedo id's. This will take time.

I would just import the data and delete out records until you have a good sampling.. Then edit the member records to be sample data by replacing any sensitive/personal data.
 
Sample DB attached, purged of actual names and reduced to a sample of records per table. This took longer than I thought because I am very sensitive to personal ID theft.

Three questions:
1. I have Access 2000 on one computer and Access 2003 on another. I am using the computer with 2003 for mdb work and it is in Access 2000 file format. If I convert it to 2002/2003 format, then I will not be able to backup the db on the computer with 2000. Could I use OpenOffice Base as a backup?
2. If I convert to Access 2002/2003, will the updated version gain me any functionality?
3. If I use the MS Access Tool 'Back Up Database', should I copy the older versions into another MS Access DB (new name)?

John
 

Attachments

1. I have Access 2000 on one computer and Access 2003 on another. I am using the computer with 2003 for mdb work and it is in Access 2000 file format. If I convert it to 2002/2003 format, then I will not be able to backup the db on the computer with 2000. Could I use OpenOffice Base as a backup?

I would keep the database in 2000 format. If you upgrade the the format to 2002/2003, then the computer with Access 2000 will not be able to open the database

You can back up the database using any backup software. I like to use some type of ZIPPing app that will actually compress the file. There is one built into WIN XP that works.

I never use the built in backup feature of Access. All it does is make a copy of the database and change the file extension.


2. If I convert to Access 2002/2003, will the updated version gain me any functionality?

If you upgrade the format to 2002/2003 , then you must use Access 2002/2003 or later to open the database. You will no longer be able to use Aces 2000 to open the database. If your are planning on purchasing an upgrade of Access from 2000 to 2002/2003, then I would wait to convert the format until you have the new software purchased and installed.


3. If I use the MS Access Tool 'Back Up Database', should I copy the older versions into another MS Access DB (new name)?

I keep lots of backups of my databases. I actually back up every database before I open it for any reason.


I will take a look at your database and get back with you...
 
Last edited:

Users who are viewing this thread

Back
Top Bottom