Bank related Transactions

[hhfreddie]

Hullo guys, hope you are fine.

I need some extensive help. Am working on a database whose transactions relate to those of a financial institution.

I have so far created the following tables;
-tblMembers Table (MemberID and Name, etc)
-tblClient Table (ClientID, Name, etc)
-tblMemberDeposits (DepositID, MemberID, etc)
-tblloans (LoanID, Member/ClientID, etc)
-tblLoanPayback (PaybackID, LoanID, etc)
-tblExpenses (ExpesesID, Name, etc)
-tblLoanStatus (StatusId, StatusName - completed- in progress, etc)
-tblUsers (UserID, Name)


Notes:
1. - This is a group of Individuals whose details are taken care of by tblMembers
2. - tblClients represents outsiders to the group who come in for small loans
3. - Both members and clients can take loans but at different interest rates per month i.e. 5% for members and 10% for Clients.
4. - tblMemberdeposits captures member’s personal deposits which in return are given out as loans because it form the group capital base.
5. - Whenever a member makes a capital deposit, his or her capital base increases and so gets a new share percentage (MemberTotalDeposits/TotalDepositsOfAllMembers)*100 for that particular period
6. - Members’s deposits are supposed to grow as more interests are obtained from loans per month depending on each member’s cumulative capital base (share percentage). Note that (a) Total month expenses should be offset from the month’s gross profits first (b) it is a group policy to retain 50% of final profits per month and members share the remaining 50% depending on their share percentage.
7. - At the end of the year, a member can choose to withdraw some or all his profits accumulated throughout the year and this obviously reduces his/her share percentage.

What to be done
My biggest problem is the logical interpretation and implementation project in access as pertains to issues like;
- - The queries to run
- - Changing existing data (figures) as new deposits and withdraws are made
- Additional tables if any
- Etc


So please whoever has a clue / resourse or has ever implemented such a project to give me a hand, I will be glad.
I have also attached a sample database for what I have done sofar.
[FONT=&quot]
Thanks[/FONT]

 

[CJ_London]

To simplify things, I would merge your tblMembers and tblClients into one table and have an additional field, CustomerType (member or client).

However there is still some muddy thinking around your structure.

• What if a member makes a deposit so is classified as a member, then takes out a loan at the beneficial rate of 5% and then withdraws their deposit - do they then become a client?
• what happens if total expenses for month exceed gross profit? Do the member take a hit or is the 'loss' taken from reserves (the 50% not distributed)
• How are you going manage expenses which are annual rather than monthly? - spread evenly through the year? hit in the month of expense.
• What is the date of your expense - the date you incur it or the date you pay it?
• No mention of regulatory requirements

To get started, you really need to create some clear business rules around process, Status change/event management (example 1 above has 3 events and 4 status's) and reporting requirements before you can really determine the data structure and subsequent queries required.

 

[hhfreddie]

Thanks C.J 4 the reply and the key notes;

To simplify things, I would merge your tblMembers and tblClients into one table and have an additional field, CustomerType (member or client).

Thats how i had treated it but then, it is a group policy that for a client to get a loan, has to be refered by a member. Thats why i sprit them for the referal field to have a lookup to members table

What if a member makes a deposit so is classified as a member, then takes out a loan at the beneficial rate of 5% and then withdraws their deposit - do they then become a client?

No, members are like the owners of the group. So whether they deposit or not remain members.

what happens if total expenses for month exceed gross profit? Do the member take a hit or is the 'loss' taken from reserves (the 50% not distributed)

In a month total expenses exceed profits, there are no paid out profits to members and instead, either members can contribute from personal pockets to the dificit or it can be offset from the general pool according to members' resolution

How are you going manage expenses which are annual rather than monthly? - spread evenly through the year? hit in the month of expense.

I think spreading it evenly per month is the accounting principal to take here.

What is the date of your expense - the date you incur it or the date you pay it?

The assumption is, it is paid when incured

No mention of regulatory requirements

This is like an inhouse business. its like a circle. there are no regulatory costs. If any, will be treated as expenses.

 

[CJ_London]

i sprit them for the referal field to have a lookup to members table

- you can still do this, using the customertype field as a filter, however I can see your reasoning - but it will make managing of your loans more complex.

With regards the rest, sounds like you have some rules but to help with what to be done per your original post, you will need to provide detail as you go.

Always provide the full details for relevant tables (table name, fieldnames and type) and describe their relationship where appropriate. Ideally also provide some example data and in the case of queries an example of what you want to end up with.

Ideally avoid using reserved words and spaces in field, control and table and query names - their use will extend your development time exponentially!

 

[hhfreddie]

Thanks for the response JC

you can still do this, using the customertype field as a filter, however I can see your reasoning - but it will make managing of your loans more complex.

I have not used filters at all, actually i have no idea about them. how can i achieve that in this case?

 

[CJ_London]

Ok a filter is basically just another name for a criteria

so if your basic query is

SELECT * FROM tblMembers

Which will return all of the records and fields in tblMembers - equivalent to opening and viewing the table.

If you just want to look at members (assuming you have taken my advice) then the query with criteria (the WHERE part) would be something like

SELECT * FROM tblMembers WHERE MemberType='Member'

or you want to look at clients

SELECT * FROM tblMembers WHERE MemberType='Client'

The filter part of this is

MemberType='Client'

So if your form has a recordsource of tblMembers your filter would be

MemberType='Client'

Best thing to do is practice. If you have not used filters or criteria before you will be extremely restricted in what you can do until you learn how to use them.

 

[hhfreddie]

filter is basically just another name for a criteria

Thanks alot JC