none
Using Access for Contrabuttions RRS feed

  • Question

  • Good day All,

    I use Access to record the Tithes, Offerings and special donations from my church.  The problem that I am having is that a giver can mark several donation on one envelope, but I have to pull their name up for each donation. Is there a way to enter a donors contributions all at once in one record?

    Please Help!

    Tuesday, January 22, 2019 6:47 PM

All replies

  • Hi,

    There is, but it's not really recommended. Each contribution should be stored separately to make it easier to retrieve and analyze. Are you not using a form/subform setup to enter the information? If you have a form/subform for data entry, you wouldn't have to "pull" their name for each donation.

    Tuesday, January 22, 2019 6:57 PM
  • You need three tables, in broad outline as follows:

    Donors
    ….DonorID  (PK)
    ….FirstName
    ….LastName

    DonationCategories
    ….DonationCategoryID  (PK)
    ….DonationCategory

    and to model the many-to-many relationship type between them:

    Donations
    ….DonationID  (PK)
    ….DonationDate
    ….Amount
    ….DonorID  (FK)
    ….DonationCategoryID  (FK)

    The interface for data entry would be a form, in single form view, based on Donors, and within it a subform based on Donations, in continuous forms view.  The subform would be linked to the parent form on DonorID.  The primary keys in each case can be autonumbers, but the corresponding foreign keys must be of straightforward long integer number data type.

    With this interface you can insert as many rows as you wish in the subform, each representing a donation by the donor currently selected in the parent form.

    Ken Sheridan, Stafford, England

    Tuesday, January 22, 2019 10:14 PM
  • This is a classic check (or cheque) distribution problem.

    So just like “any” accounting system,  you often have one check, and then have to assign that single amount to several accounts.

    So in fact, you often have ONE donation, but must split out those amounts to several categories.

    Or in that envelope you might have two separate donations (say some cash, and one check.

    It not clear if you ALWAYS take the total amount from a given person and THEN split out to several accounts.

    However, your setup should allow for both cases.

    This form in Access shows how I solved this problem.

    Note on the left side we have the person + donation amount, but on the right side of the form, we have the single amount split out to several possible accounts.

    How fancy you want to get is really up to you.

    So, what the above means is you have to design some tables that allow you to do this.

    tblDonationEvent.

    Above will be the date, the event (wedding, morning event, afternoon, whatever). Who presided over the event etc. So you can describe the event, date, time, who presided etc. In above, this is the  top part of the form

    tblDonationAmount

    This is a table that lets you enter the full amount given, and select who made the donation. (in above, that is the left side (a sub form). This table will be related to the above tblDonationEvent. This table also of course also lets you select/choose the “member” (so a members table is required, but not shown here). That members table drives the combo box in above for ease of data entry, but ALSO so you cannot misspell or type in the member name.

    tblDonationAccounts

    This is a table (in above on the right side). This table just lists out the account number, and the amount you assigned from that single donation to a given account. So, you choose (again a combo box to prevent errors, and select the account). Often the amount will be 100% of the donation, but if you have MORE than one category to assign the full amount, then you can enter “many” accounts and amounts – the form should check and verify that the total of each part adds up to the single donation from the envelope. In above I have a cute green box (it goes green when amounts match).

    To make a long story short?

    The design of what we call a “data model” is the key concept in making software do what you want. Once you get the data structures correct, then the building of the user interface part becomes far more easy of a task.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada


    Wednesday, January 23, 2019 7:53 PM