Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    An approach request (Access XP)

    Working on a database for a small association. They want to generate invoices for dues, which can be an annual, quarterly, or monthly payment. Some members are also billed for another service, quarterly payments. I have a table with the basic fees and payment (annual, quarterly, or monthly) in the database.

    They worked off an Excel spread sheet before and did not keep a record of previous billings in the spreadsheet. They had a column for total billings and past due amounts. I think I want to set up a table that would have a row for each invoice for dues, WC payments, date of bill. Then a place where they can receipt the payment amount and payment date so that past due amounts can be figured out so past due notices can be sent.

    Big ideas, but not sure on implementation. Any approach ideas? Thanks in advance. Fay

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: An approach request (Access XP)

    Lots of ideass - not sure how useful or germane they are, but here goes . . .
    What they are looking for is a small piece of a single ledger accounting system. Membership systems often have this requirement, and often don't want to try to track it using a conventional accounting system because of the number of detail records involved, and because in many cases they can't really book the revenue until some pays them, so an Accounts Receivable function isn't really practical.

    So what we've done in the past is create a transaction table that tracks each billing and each payment, and keeps a running balance so things can be audited when they get out of whack. The field details typically include a foreign key to the membership table, and indicate what kind of record, payment info such as check #, CC# and such, or in the case of billing, what was billed, what the due date was, amount billed, etc. You will need fairly strict controls on the editing and/or deleting of records from the table. Guess that's about it - feel free to post back with questions.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: An approach request (Access XP)

    Hi Fay,

    You might get some ideas from the Microsoft Office Template Gallery, go to http://search.officeupdate.microsoft.com/T...t.asp?qu=Access and check out the "Club Membership Database". While I don't always agree with how some of Microsoft's sample applications are setup, they do usually give you some good ideas to start from.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: An approach request (Access XP)

    Hi Fay
    I have done this a few times and the tables I setup were:

    People table that holds information re the people in the association:
    PeopleID
    Surname
    Firstname
    etc

    Invoices table hold details of invoices for people:
    PeopleID
    InvID
    DateInvoiced
    Amount
    etc

    Payments table holding receipts that people have paid:
    PeopleID
    PayID
    Amount
    DatePaid
    etc

    PaymentsAgainstInvoices table is a cross reference that matches payments against invoices (this is optional, it depends how far you wish to go):
    InvID
    PayID
    AmountApplied
    etc

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •