Results 1 to 11 of 11
  1. #1
    dawgdog is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    6

    Database with Multiple Payment Types and Events

    Hi I am helping out an organization that asks for members to contribute money (cash, check, or money orders) when another member dies to help pay for their funeral and am trying to create a database for them. They have been using Excel to track this where membership fees and funerals are the row headers and the contributors are in the row. Every time they get a new batch of contributions they add a new row to the sheet and it has become quite long and full of empty space. For example, if there were 4 deaths in one month, the contributor would have 4 separate rows, one for each death. If the deaths are close to each other the contributor will sometimes send in one check for multiple deaths and that just gets added as one row. Also some checks will be from multiple people so that will have to be added as two separate rows.

    I have read and watched some basic tutorials and am wondering how is the best way to organize the tables and relationships. I am not sure exactly how to create the donation table (membership dues, funeral contribution) because each payment can be accompanied by additional donations which splits the amount between contribution and donation.



    And they want to start tracking when each payment is made and I know there should be a way to generate a report of based on members' payment history, funeral contributions, or payment dates.

  2. #2
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    553
    Hi
    Can you upload a zipped copy of the Excel file with no confidential data?

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,963
    Are contributors only ever members, or might you receive funds from a non-member (e.g. spouse of member who may be deceased)?
    Will you only ever use this db for funeral expense contributions or can you foresee possibly expanding to other events/reasons?
    How would you want to deal with contributions meant for multiple events? Simplest would be to record the same contribution ID to multiple events. Divvying up the funds would be more complicated I think.

    I get no clue as to your level of expertise. If it's extreme novice, I have a bunch of links I could post on a variety of subjects that will help you avoid many common pitfalls. Anyone who helps can just jump in with suggestions but you might not understand some of it.
    Last edited by Micron; 11-14-2020 at 12:16 PM. Reason: clarification
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    Join Date
    Apr 2017
    Posts
    1,204
    Quote Originally Posted by dawgdog View Post
    ... the contributor will sometimes send in one check for multiple deaths
    1. Is there some rule how the check will be distributed between deaths (e.g. evenly), or not?
    Also some checks will be from multiple people ...
    2. Is there some rule how the check will be distributed between contributors (e.g. evenly), or not?

    3. Are cases where the donation is for several deaths AND from several contributors possible? And what about cases where donation is for several deaths from several contributors, but every contributor has different set of deaths he/she is contributing to?

    A possible structure:
    A table for deaths/causes (The later when it is possible you expand your donation system for other causes. The table contains among other fields a field for a date bound to death/cause);
    A table for contributors;
    A table for donations/checks (The table contains among other fields fields for total of incoming amount, and for donation date);
    Further I assume that there is no set rule how donations are distributed between contributors and/or causes (The worst case. In case 3. is true, it is given one! In case both 1. and 2 are true and 3. is false, you can use formulas to distribute donations and the field for distributed amount in distribution table may be skipped - but database may get somewhat slower to work with.).
    A table where the donation is distributed between causes and distributors. E.g. tblDistribution: DistrID, DonationID, ContributorID, CauseID, DistrAmount.

    There may be additional tables depending on your needs.

  5. #5
    dawgdog is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    6
    Here is an upload of the Excel file.

    The October sheet shows the issues I am running into the most. The last entry, Kunthary De Gaiffer, has submitted one check for all the death of the year and we have to keep the columns for all the deaths to track her contribution. This creates a lot of empty cells on the sheet.
    Attached Files Attached Files

  6. #6
    dawgdog is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    6
    Quote Originally Posted by Micron View Post
    Are contributors only ever members, or might you receive funds from a non-member (e.g. spouse of member who may be deceased)?
    Will you only ever use this db for funeral expense contributions or can you foresee possibly expanding to other events/reasons?
    We actually just had this issue come up in the past month. There was a flood disaster and the organization is asking for donations for flood relief. Another organization, not a member, just donated to us. So before I would have just had two categories, membership and funeral, now there could be a third category. Or they could all be treated as

    Quote Originally Posted by Micron View Post
    How would you want to deal with contributions meant for multiple events? Simplest would be to record the same contribution ID to multiple events. Divvying up the funds would be more complicated I think.
    Yes, this is the part I am trying to resolve. I have uploaded a zip file of the Excel file of the current month so you can see what I mean. When they send the money they have which funerals the contributions go to.

    Quote Originally Posted by Micron View Post
    I get no clue as to your level of expertise. If it's extreme novice, I have a bunch of links I could post on a variety of subjects that will help you avoid many common pitfalls. Anyone who helps can just jump in with suggestions but you might not understand some of it.
    I have taken a basic class to learn for work and I do deal with some database for work, but that is mostly data entry and manipulation. So I know that a database is the best way to organize all this data, but I have not created one from scratch before. I have watched some videos and downloaded some templates to try and get an idea of how to do this.

  7. #7
    dawgdog is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    6
    Quote Originally Posted by ArviLaanemets View Post
    1. Is there some rule how the check will be distributed between deaths (e.g. evenly), or not?

    2. Is there some rule how the check will be distributed between contributors (e.g. evenly), or not?

    3. Are cases where the donation is for several deaths AND from several contributors possible? And what about cases where donation is for several deaths from several contributors, but every contributor has different set of deaths he/she is contributing to?
    1. We send flyers out for the deaths so when they send checks there is a sheet they send back with for the person so we know who to distribute it too.

    2. They will write/indicate which member is contributing how much and for which death.

    3. So far we have only had one member contributing to multiple deaths or multiple members contributing to one death issue but I can see that possibility happening.

    Quote Originally Posted by ArviLaanemets View Post
    A possible structure:
    A table for deaths/causes (The later when it is possible you expand your donation system for other causes. The table contains among other fields a field for a date bound to death/cause);
    A table for contributors;
    A table for donations/checks (The table contains among other fields fields for total of incoming amount, and for donation date);
    Further I assume that there is no set rule how donations are distributed between contributors and/or causes (The worst case. In case 3. is true, it is given one! In case both 1. and 2 are true and 3. is false, you can use formulas to distribute donations and the field for distributed amount in distribution table may be skipped - but database may get somewhat slower to work with.).
    A table where the donation is distributed between causes and distributors. E.g. tblDistribution: DistrID, DonationID, ContributorID, CauseID, DistrAmount.

    There may be additional tables depending on your needs.
    So the donations/checks table will be the longest table? And then the last table you mentioned will be the table linking to all three other tables?

  8. #8
    Join Date
    Apr 2017
    Posts
    1,204
    Quote Originally Posted by dawgdog View Post
    So the donations/checks table will be the longest table? And then the last table you mentioned will be the table linking to all three other tables?
    I think the longest will be donations distribution table (at least 1 row for every donation, but as most of fields are primary or foreign keys, and the number of fields is small, it probably will be smaller in bytes). And yes, it is linking all those tables.
    The last entry, Kunthary De Gaiffer, has submitted one check for all the death of the year and we have to keep the columns for all the deaths to track her contribution.
    This is a complication! To cope with it, :
    a) either donations distribution table is filled manually until donated sum is spent or the year has passed (you have to check the total of distributed amount for donation, and restrict distribution more than total amount in donations table);
    b) or you have some additional table where distribution rules are defined, and an additional field in donations table, which determines which rule is used. And you need an additional fields in donations table which indicate, when was last distribution initiated, and is it closed for distribution or not. And some form event (e.g. Open for main form) will check all donations which aren't closed for distribution and which have next distribution date empty or less than current date. And when next distribution date is empty or less than current one, it initiates distribution for this donation following rules set for this donation. A lot of heavy coding, as you see!

  9. #9
    dawgdog is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    6
    I am trying to make these tables now and right now the Distribution table is very similar to the Payments table, but it will have just a few extra lines because each Payment will go into the Distribution table, with a few dozen extra entries per death from people sending in money together or paying for multiple deaths at once. Does that feel like it's the right way to go?

    And for distribution, that is something that people mark when sending in payments so we will be doing that manually.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,204
    Quote Originally Posted by dawgdog View Post
    Does that feel like it's the right way to go?
    It looks like it does.

  11. #11
    dawgdog is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    6
    Ok, thanks for all the help you've given me!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-21-2017, 06:52 AM
  2. Replies: 11
    Last Post: 11-28-2015, 10:14 PM
  3. Run multiple reports for multiple asset types
    By cphelps in forum Reports
    Replies: 1
    Last Post: 09-08-2013, 09:01 PM
  4. Replies: 1
    Last Post: 08-09-2011, 11:13 AM
  5. Multiple Payment Instances
    By luckysarea in forum Queries
    Replies: 3
    Last Post: 04-21-2011, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums