Database Examples: The Associates
 

Introduction

In some parts of the world and some communities, people have a weird way of saving money without using a bank. A group of 4, 6, 12, or more people get together (less than 6 is not very realistic and more than 20 may be too many, although it depends on how frequent they "meet", as we will see).
 

Practical LearningPractical Learning: Introducing the Associates
  • If you want to follow this exercise, create a blank database named The Associates

The Number of Participants

The first time this group of people get together, they make a few decisions. The first action consists of evaluating the number of people who are going to participate and they register them (sometimes the registration is mental because everyone knows the other people).

Practical LearningPractical Learning: Registering the Participants

  1. Before registering the participants, start a table in Design View and create it as follows:
     
    Field Name Data Type Caption Additional Properties
    ParticipantID AutoNumber Participant ID Primary Key: True
    AccountNumber Text Account # Field Size: 10
    Indexed: Yes (No Duplicates)
    AccountDate Text Account Date  
    FullName Text Full Name  
    HomePhone Text Home Phone  
    CellPhone Text Cell Phone  
    EmailAddress Text Email Address  
    Notes Memo    
  2. Save the table as Participants
  3. Close the table

The Leader

To centralize the management of this organization, somebody has to be in charge. It doesn't have to be the person who initiated the organization. When people get together for the first time, they also designate who would be the leader. A leader is in charge of coordinating the transactions (we will see what types of transactions, when and how), "animating" the members, keeping the group informed, applying the service charges, etc. For this exercise, we will refer to this leader as an account manager. Therefore, this person will be in charge of creating and keeping accounts, like a bank.

A leader can have an assistant who can be reached if/when the leader is not available.

Practical LearningPractical Learning: Designating a Leader

  1. Open the Participants table in Design View and add a new field between EmailAddress and Notes. Name it IsAnAccountManager and set its Data Type to Yes/No:
     
    Field Name Data Type Caption Additional Properties
    ParticipantID AutoNumber Participant ID Primary Key: True
    AccountNumber Text Account # Field Size: 10
    Indexed: Yes (No Duplicates)
    AccountDate Text Account Date  
    FullName Text Full Name  
    HomePhone Text Home Phone  
    CellPhone Text Cell Phone  
    EmailAddress Text Email Address  
    IsAnAccountManager Yes/No Account Manager? Display Control: Combo Box
    Notes Memo    
  2. Save the table and fill it up with a few records:
     
    Account # Account Date Full Name Email Address Account Manager?
    97-395 06/01/2002 Douglas Coulomb coulomd@yahoo.com No
    24-759 06/01/2002 Hermine Kats katsh@cpasonline.net Yes
    29-375 06/01/2002 Tony Peres perestroika@netscape.net No
    74-929 06/01/2002 Françoise Nguyen nguyenf@monitorsystems.com No
    99-275 06/01/2002 Helene Mafany mafanyh@emailcity.com No
    85-487 06/01/2002 Juanita Klebers klebersj@usantabas.edu No
    77-597 06/01/2002 Alexander Wayne waynea@hotmail.com Yes
    79-437 06/02/2002 Jeannine Monay monayj@yahoo.com No
    38-685 06/02/2002 Kelley Graham   No
    93-457 06/02/2002 John Simms   No
    38-508 06/03/2002 Gabrielle Kumar   No
    35-798 06/05/2002 Jeffrey Lamm   No
  3. Close the table
  4. Using AutoForm, generate a form based on the Participants table and design it as you see fit:
     
    The Participants Form: Design
     
    The Participants Form: Result
  5. Save the form as Participants

Contributions

The reason people get together in this organization is to save money. To do that, they decide on the frequency they will meet. Different factors influence this aspect, based on the number of participants, a pledge amount and the common financial means. We have covered the number of participants.

Probably the next decision would be on the frequency of meetings. These people typically meet (they usually don't have to physically meet but this issue must be decided upon, then taken care of one way or another) every week, every two weeks, or every month. Meeting every day is not realistic. Meeting every couple of months, every three months, every four months, or every semester is not useful either. This frequency should be practical to everyone because at every frequency, each member should be able to come up with the amount of money of the next section.

The next decision may be with regards to a certain factor of an amount of money. When people meet (once again, remember that they don't necessarily physically meet), each one brings a certain amount of money (if they don't physically meet, each one gives this money to the person in charge or the assistant to this person). This amount should be neither too low nor too high but it must be affordable to every participant. If the group is made of struggling workers who make around $1600 to $3000 a month and they meet every two weeks, an amount between 100 and $200 should be fine. If the group is made of successful small business owners who meet every two weeks, an amount of $300, $500, $750 should be enough. If the group is made of big company executives who meet every two weeks, the amount can be higher. If the same groups mentioned meet every week, the amount can be divided by half. If the people in the same groups we mentioned prefer to meet once a month, the amount of money can be either doubled or taken at 1.50 of that of the two-week frequency mentioned above.

The Calendar of Money Remittance

In case you are not familiar with this system, when the people meet, the money each one brings is handed to one of the participants. The next time they meet, the money brought by the participants is given to another person. This continues until each participant has received the money at least once. Based on this, the participants create a calendar that specifies each one's turn. Two people can also decide to switch turns. They would have to notify everybody and definitely the person who is in charge of the organization.

We mentioned above that the participants don't have to physically meet to perform the transactions. When the frequency (as every week, every two weeks, or every month) is up, each participant can give his or her money to a person in charge (remember that this organization is made of people who know each other and trust is extremely important here). This person is in charge of reaching the right recipient and giving the pledged money.

Practical LearningPractical Learning: Creating a Calendar

  1. Open the Participants table in Design View
  2. Insert a new field between the IsAnAccountManager and the Notes fields
  3. Name it ReceptionDate with the Caption as Reception Date
     
    Field Name Data Type Caption Additional Properties
    ParticipantID AutoNumber Participant ID Primary Key: True
    AccountNumber Text Account # Field Size: 10
    Indexed: Yes (No Duplicates)
    AccountDate Text Account Date  
    FullName Text Full Name  
    HomePhone Text Home Phone  
    CellPhone Text Cell Phone  
    EmailAddress Text Email Address  
    IsAnAccountManager Yes/No Account Manager? Display Control: Combo Box
    ReceptionDate Text Reception Date  
    Notes Memo    
  4. Save the table and switch it to Datasheet View
  5. Create the calendar as follows:
     
    Full Name Reception Date
    Douglas Coulomb 09/08/2002
    Hermine Kats 06/30/2002
    Tony Peres 10/20/2002
    Françoise Nguyen 07/14/2002
    Helene Mafany 11/17/2002
    Juanita Klebers 08/11/2002
    Alexander Wayne 10/06/2002
    Jeannine Monay 06/16/2002
    Kelley Graham 11/03/2002
    John Simms 08/25/2002
    Gabrielle Kumar 07/28/2002
    Jeffrey Lamm 09/22/2002
  6. Save and close the table
  7. Open the Participants form in Design View and, using the Field List, add the new ReceptionDate to the form between the EmailAddress and the Notes text boxes
  8. Switch the form to Form View
     
    The Participants Form With the Reception Date
  9. Save and close the form

Transaction Types

When a participant brings his or her money, the money is registered (sometimes the registration is simply mental since people know each other) to keep track of who has pledged. There can be variances. For example, suppose that the members have decided that the regular amount to pledge is $250 every two weeks. Some time to time, some people in the group would "fall short", that is, they would not have the full pledged amount. In this case, they can still contribute with the amount they have. The person receiving the money would be notified to pledge the same amount when the other person's turn comes up. In some cases, somebody P can ask a recipient R if he or she needs more money. If the recipient R agrees, the participant P could pledge more than the required amount. When the participant P's turn to receive money comes up, the other recipient R must remember to pledge the same amount that participant P had pledged for him or her.

To keep people disciplined, the organization can also decide that, if people are supposed to bring their money on a certain day such as Saturday, people could be given a grace period until the next day, such as Sunday. Anyone who brings money later than that could be charged a penalty fee. In fact, somebody who is showing a lack of discipline, such as somebody who is always bringing his or her money late could be asked to "leave" before it gets worse. In this case, such a delinquent would receive money only from those who had gotten his or her money in the past.

We mentioned earlier that two people can switch turns. This happens if a person whose turn is not up suddenly needs money (funeral, marriage, kid school fee, etc) and would not like to wait. As another variance, some groups allow people to borrow money. This is left to the participants understanding.

It is important to know that, inevitably, there can be problems of any kind but we can't get into them...

Practical LearningPractical Learning: Specifying Transaction Types
  1. Create a new table in Design View as follows:
     
    Field Name Data Type Caption Additional Properties
    TransactionTypeID AutoNumber Transaction Type ID Primary Key: True
    TransactionType   Transaction Type  
    Notes Memo    
  2. Save the table as TransactionTypes and fill it up as follows:
     
    TransactionTypeID TransactionType Notes
    1 Deposit  
    2 Reception  
    3 Service Charge  
    4 Loan  
  3. Close the table
  4. Using AutoForm, generate a form based on the TransactionTypes table and design it as you see fit:
     
    Transaction Types: Form Design
     
    Transaction Types: Form Result
  5. Save the form as Participants

Transactions Setup

There are two regular transactions that occur in this organization. As mentioned already, people in this organization don't always physically meet, although this is the routine. A person can simply bring the money to the person in charge. The person in charge must register the transaction by noting who brought the money and how much. As the second most regular transaction, once the money is ready, it is handed to the person whose turn this is. Also, if a person brings the money late, the account manager must specify whether a penalty is applied or not. Normally, there is a certain time decided when the recipient should receive the money. The people in the group decide on this. For our example, we will consider that all the money should have been collected by Sunday at 6PM and the account manager will give the money to the appropriate recipient by 10PM. Therefore, anybody who brings the money after that period will suffer a penalty of $20.

Practical LearningPractical Learning: Setting Up Transactions

  1. Start a new table in Design View
  2. Set the first Field Name to TransactionID, its Data Type to AutoNumber, right-click it and click Primary Key
  3. Save the table as Transactions
  4. Create the second column as follows:
    Field Name: TransactionDate
    Caption: Date
    Default Value: =Date()
  5. Set the third Field Name to Processed By and set its Data Type to Lookup Wizard
  6. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
  7. In the second page of the wizard, click Participants and click Next
  8. In the Available Tables list, double-click FullName and click Next
  9. In the fourth page, click Next
  10. In the fifth page, click Finish
  11. When asked whether you want to save the table, click Yes
  12. In the lower section of the table in Design View, change the Default Value to 2
  13. Click Lookup and click the ellipsis button of the Row Source field
  14. To make sure that only those in charge can perform transactions, in the first empty column, select IsAnAccountManager
  15. Clear the check box of its Show field and, in the corresponding Criteria field, type True
     
    Query Window: Preparing to display only account managers in a combo box
  16. Close the query window
  17. When asked whether you want to save the statement, click Yes
  18. In the upper section of the table, change the name of the column from ParticipantID to AccountManager
  19. Create the next column as follows:
    Field Name: TransactionNumber
    Data Type: Number
    Caption:     Trans #
    Delete 0 in the Default Value
    Indexed: Yes (No Duplicate)
  20. Set the next Field Name to Participant and set its Data Type to Lookup Wizard
  21. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
  22. In the second page of the wizard, click Participants and click Next
  23. In the Available Tables list, double-click AccountNumber and FullName
  24. Click Next
  25. In the fourth page, click Next
  26. In the fifth page, click Finish
  27. When asked whether you want to save the table, click Yes and delete 0 in the Default Value
  28. Set the next Field Name to Trans Type and set its Data Type to Lookup Wizard
  29. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
  30. In the second page of the wizard, click TransactionTypes and click Next
  31. In the Available Tables list, double-click TransactionType and click Next
  32. In the fourth page, click Next
  33. In the fifth page, click Finish
  34. When asked whether you want to save the table, click Yes and change the Default Value to 1
  35. Create the next column as follows:
    Field Name: TransactionAmount
    Data Type: Currency
    Caption:     Trans Amt
    Default Value: 250
  36. Create the next column as follows:
    Field Name: ServiceCharge
    Data Type: Number
    Caption:     Service Charge
    Delete 0 in the Default Value
  37. Set the next Field Name to Recipient and set its Data Type to Lookup Wizard
  38. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
  39. In the second page of the wizard, click Participants and click Next
  40. In the Available Tables list, double-click AccountNumber and click Next
  41. In the fourth page, click Next
  42. In the fifth page, click Finish
  43. When asked whether you want to save the table, click Yes. You will receive another warning, click OK
  44. Delete 0 in the Default Value
  45. Change the Field Name from ParticipantID to Recipient
  46. Add a new column whose Field Name is Notes and Data Type is Memo
     
    Transactions Table: In Design
  47. Save the table and close it
  48. Using the Participants table, create a form saved as sbfParticipants that includes the following fields: AccountDate, FullName, HomePhone, EmailAddress:
     
    Participants Sub Form
  49. Save and close the sub form
  50. Using the Transactions table, generate an AutoForm and design it as follows:
     
    Transactions Form: Preparing to add a sub form: In Design
     
    Transactions Form: Preparing to add a sub form: Result
  51. Save the form as Transactions
  52. Switch the form back to Design View
  53. On the Toolbox, make sure the Control Wizard button is clicked. Click the Subform/Subreport button and click the empty right side of the form
  54. In the first page of the wizard, click sbfParticipants and click Next
  55. In the second page of the wizard, make sure the first radio button is selected and click Next
  56. In the last page, click Finish
  57. Change the caption of the label that was added, from sbfParticipants to Participant Contact Information
     
    Transactions Form With a New Sub Form: In Design
     
    Transactions Form With a New Sub Form: Result
  58. Save and close the form
  59. Start a new form using the Form Wizard
  60. In the first page of the Form Wizard, in the Tables/Queries combo box, select Table: Transactions
  61. In the Available Fields, double-click TransactionDate, TransactionNumber, TransactionTypeID, TransactionAmount, ServiceCharge, and Recipient
  62. Click Next
  63. In the second page of the wizard, select Tabular and click Next
  64. In the third page of the wizard, select Standard and click Next
  65. In the fourth page, change the name to sbfTransactions and click Finish
  66. Adjust the form's design as follows:
     
    Transactions Sub Form: Design
  67. Add a Text Box control to the Form Footer section and delete its label
  68. Change its properties as follows:
    Name: txtTotalTransactions
    Control Source: =Sum([TransactionAmount])
    Format: Currency
  69. Add another Text Box control to the same Form Footer section and delete its label
  70. Change its properties as follows:
    Name: txtTotalCharges
    Control Source: =Sum([ServiceCharge])
    Format: Currency
     
    Transactions Sub Form: In Design With Two New Text Boxes
  71. Position both text boxes just under the Form Footer bar and shrink them to the lowest possible height
  72. Set their Visible property to No
  73. Also shrink the Form Footer section as much as possible
     
    Transactions Sub Form: Final Design
  74. Save and close the sub form
  75. Start a new form in Design View and save it as AccountRelatedTransactions
  76. Change the following properties
    Record Source: Participants
    Caption: The Associates - Account Related Transactions
    Allow Edits: No
    Allow Deletions: No
    Allow Additions: No
  77. Using the Field List, add the following fields to the form: AccountNumber and FullName
  78. Position the new text boxes to the upper side of the Detail section
  79. On the Toolbox, click the Subform/Subreport button and click the empty top side of the form
  80. In the first page of the wizard, click sbfTransactions and click Next
  81. In the second page of the wizard, make sure the left radio button is selected and click Next
  82. In the last page, click Finish
  83. Change the caption of the label that was added, from sbfTransactions to Account Related Transactions
  84. Add a new Text Box control to the form and change its properties as follows:
    Name: txtTotalContributions
    Control Source: =[Forms]![AccountRelatedTransactions]![sbfTransactions].Form!txtTotalTransactions
    Format: Currency
    Caption of accompanying label: Total Contributions:
  85. Add a new Text Box control to the form and change its properties as follows:
    Name: txtTotalCharges
    Control Source: =[Forms]![AccountRelatedTransactions]![sbfTransactions].Form!txtTotalCharges
    Format: Currency
    Caption of accompanying label: Total Charges:
  86. Complete the design of the form as follows:
     
    Accounts Related Transactions Form: In Design
  87. Save and close the form
  88. Start a new form using the Form Wizard
  89. Use the Transactions table as source. Add the following fields: TransactionDate, AccountManager, ParticipantID, TransactionNumber and TransactionAmount. Set the form layout to Tabular and save it as sbfRecipients
  90. In the Design View, add a new Text Box in the Form Footer section and delete its accompanying label
  91. Change its properties as follows:
    Name: txtTotalTransactions
    Control Source: =Sum([TransactionAmount])
    Format: Currency
     
    Recipients Sub Form: In Design
  92. Position the text box just under the Form Footer bar. Shrink it as much as possible and set its Visible property to No
  93. Save and close the sub form
  94. Start a new form using the Form Wizard and based on the Participants table. Select the following fields: AccountNumber, FullName, ReceptionDate and Notes
  95. Create the form as Columnar and save it as Recipients
  96. In Design View, position the AccountNumber and the FullName fields in the upper side of the Detail section. Position the ReceptionDate and the Notes fields in the lower side of the Detail section
  97. On the Toolbox, click Subform/Subreport and click an empty area in the Detail section of the form
  98. In the first page of the wizard, click sbfRecipients and click Next
  99. In the second page of the wizard, click the Define My Own radio button
  100. In the top-left combo box, select ParticipantID
  101. In the top-right combo box, select Recipient
     
    Sub Form Wizard: Custom Relationship
  102. Click Next and click Finish
  103. Change the caption of the label that was added to Money Received by this Account
  104. Add a new Text Box to the form and change the following properties:
    Name: txtAmountReceived
    Control Source: =[Forms]![Recipients]![sbfRecipients].Form!txtTotalTransactions
    Format: Currency
    Caption of accompanying label: Amount Received:
  105. Complete the design of the form as follows:
     
    Recipients Form: In Design
  106. Save and close the form
  107. Open the Transactions form and perform a few transactions as follows:
     
     
    Date Processed By Trans # Participant Trans Type Trans Amt Service Charge Recipient
    06/14/2002 Alexander Wayne 11 97-395 Deposit $250.00   79-437
    06/14/2002 Alexander Wayne 12 77-597 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 13 74-929 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 14 24-759 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 15 85-487 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 16 29-375 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 17 35-798 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 18 99-275 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 19 79-437 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 20 38-508 Deposit $250.00   79-437
    06/15/2002 Hermine Kats 21 38-685 Deposit $250.00   79-437
    06/27/2002 Alexander Wayne 31 93-457 Deposit $250.00   24-759
    06/28/2002 Hermine Kats 32 85-487 Deposit $250.00   24-759
    06/28/2002 Alexander Wayne 33 77-597 Deposit $250.00   24-759
    06/28/2002 Alexander Wayne 34 79-437 Deposit $250.00   24-759
    06/28/2002 Alexander Wayne 35 38-508 Deposit $250.00   24-759
    06/28/2002 Alexander Wayne 36 38-685 Deposit $250.00   24-759
    06/29/2002 Alexander Wayne 37 29-375 Deposit $250.00   24-759
    06/29/2002 Hermine Kats 38 99-275 Deposit $250.00   24-759
    06/29/2002 Hermine Kats 39 35-798 Deposit $250.00   24-759
    06/30/2002 Hermine Kats 40 97-395 Deposit $175.00   24-759
    07/01/2002 Hermine Kats 41 74-929 Deposit $230.00   24-759
    07/01/2002 Hermine Kats 51 74-929 Service Charge $0.00 $20.00 24-759
    07/12/2002 Hermine Kats 52 97-395 Deposit $250.00   74-929
    07/12/2002 Hermine Kats 53 99-275 Deposit $250.00   74-929
    07/12/2002 Hermine Kats 54 85-487 Deposit $250.00   74-929
    07/12/2002 Hermine Kats 55 29-375 Deposit $250.00   74-929
    07/13/2002 Hermine Kats 56 79-437 Deposit $200.00   74-929
    07/14/2002 Hermine Kats 57 24-759 Deposit $250.00   74-929
    07/14/2002 Hermine Kats 58 38-508 Deposit $250.00   74-929
    07/14/2002 Hermine Kats 59 35-798 Deposit $225.00   74-929
    07/14/2002 Hermine Kats 60 77-597 Deposit $250.00   74-929
    07/15/2002 Hermine Kats 61 38-685 Deposit $230.00   74-929
    07/15/2002 Hermine Kats 62 38-685 Service Charge $0.00 $20.00 74-929
    07/15/2002 Hermine Kats 63 93-457 Deposit $230.00   74-929
    07/15/2002 Hermine Kats 64 93-457 Service Charge $0.00 $20.00 74-929
  108. Close the Transactions form
  109. Open each of the other forms to view the result
     
    The Associates: Participants
     
    The Associates: Transaction Processing
     
    The Associates: Account Related Transactions
     
    The Associates: A Recipient's Result
     
    The Associates: Another Recipient's Result
  110. Close the forms
 
Home Copyright © 2008-2016, FunctionX, Inc.