1 Reply Latest reply on Dec 30, 2011 11:01 AM by philmodjunk

    Need help with creating Income and Dues Relational Tables



      Need help with creating Income and Dues Relational Tables


      Greetings and thank you for your time.


      I am Secretary for a Masonic Lodge.

      I have created a Member database.

      Each Member pays yearly dues and this is a source of income. Some members are excused "Remitted" from dues (Age, Hardship)

      Dues for 2012 are 80.00 for Regular members. 40.00 for Dual members. (Dual is Members who belong to another Lodge as well and that was their original  "mother" Lodge"

      Dues amounts change every year. (PS they never go down. HaHa)

      Members who don't pay within the calendar year are classified as "In Arrears". After two years they may be "suspended" and lose mebership.

      The Lodge has other sources of income eg. donations, fundraisers

      I want to be able to set up two tables 1. Income 2. Dues

      I want to be able to enter all income, whether Dues or Other, via the Income table

      That would mean that when the category in the income table is "Dues" then that record would populate over into the dues table.


      I am having trouble thinking how to do this, and wondering if I even need a seperate "Dues" Table


      The idea on a report would be to find all members dues status. That status could be in the Member table.

      who pays what - Regular 80  or Dual 40

      who's dues are outstanding for each calendar year

      if status is "Paid in full" or "Current" then all is good

      otherwise  if within calendar year "still outstanding" is returned

      if 1 year behind "in arrears" is returned

      if 2 years behind "in jeopardy of suspension for NPD" is returned (Non Payment of Dues)


      Maybe I just need an income Table then do a search for Category "Dues" then a whole bunch of comparisons need to be done.


      Any way. All help is appreciated.


      Thank you. 

        • 1. Re: Need help with creating Income and Dues Relational Tables

          I think reporting will be easier if you have a record in your Income for all instances where your orginization receives income--whether dues, donations or other funds. These records can be linked to your members table to enable you to see which members have paid their dues. I'd even log all expenses, using a separate field, in this table so that I can set up a typical accounting ledger type view of all income and expense transactions.

          Your income table might have these fields:

          __pk_IncomeID (an auto-entered serial number to serve as the primary key)
          Transactiondate (date field)
          TransType (Type of transaction, on value in this field would be "Dues")
          Income (Number, dollar amount recieved)
          Expense (Number, Dollar amount disbursed)
          cBal (calcualtion field, Income - Expense )
          Balance ( SummaryField, Total of cBal)
          _fk_memberID (Number, links to __pk_MemberID field in the members table, leave this field empty for non dues transactions)

          A portal to this table on a member based layout can be used to record dues when paid by that member. It could use this relationship:

          Members::__pk_MemberID = IncExpLedger::_fk_memberID

          If you define a relationship between members and this table that is sorted in descending order by date, the most recent dues payment will be the first related record and thus you can set up a report that lists all members and their most recent dues payment. Comparing the TransactionDate of that payment record to the current date will then enable you to determine their membership status.