Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    9

    table enhancement

    I'm keeping track of finances for a charitable club of 55 members and created two tables: (1) a table for entering dues, meals, and other charges and (2) a table of transactions for deposits and withdrawals. I have queries for each member using the unique member id, to extract information on members account status.
    When I bill a member the total (meals, dues, other) balance is provided. The payment is a single check.
    I need to report on the status of dues, meals, etc., individually, but am having difficulty constructing a set of codes to associate with each type of bill category, so that I could run a query of the members table and extract data by category, and sum. The balance due is a sum of charges and the payment is for all categories.
    It initially seemed simple for me to have a code field in the table and meals, for example, would have the associated "MEA" code with the meal charge. Dues and other charges would alos have unique codes.
    When the members check arrived, I would proportion the amount against a set of corresponding payment codes.
    It is not working and I need to develop some type of test, or recode, or separate each type of charge in separate tables, or?

    Any advise?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I think you need to sit back and think about what you want before doing

    From my perspective you need a table whcih identifies members
    A table that identifies suppliers
    A table whcih identifies transaction codes (arguably also identifying whether that transaction is positive or negative). that would identify meals, membership dues etc.....)
    Whether its worth running a Purchase table AND a Sales table (effectively membership dues are Sales) or not I don't know, thats down to what you want and what you are comfortable with. if you put them into one table you have a problem of maintaining the data integrity......

    A Table which identifies transactions (whiich associates a member OR a supplier with a specific transaction.

    I think you shoudl reconsider what you are doing right now... and make sure that wahtever you are doing in Access doesn't compromise the club's activities.

    If it were me I'd have a purchase ledger and a sales ledger, heck for what it costs I'd probably go out and buy a copy of Sage/TAS books for around £50, then at the worst all you have to worry about is miscoding an payment or receipt, as opposed to a mis coding in VBA/forms or reports that causes chaos.

    I'd have separate tables for members (on the Sales side)
    Id have a separate tabel for suppliers on the purchase side

    You definately do not want to have multiple tables on for each type of charge. its arguable that if the charges are truly distinct then you could have more than one table.. but it would need to be a truly exemplary reason.
    if you need to apportion a payment recieved then you need an invoice and invoice details to allocate a payment against invoice(s). you need to know what is outstanding
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2008
    Posts
    9
    I have separate transaction tables for administrative and chavirites accounts.
    IRS requires this so people who donate to charities can deduct from their income tax. Charities deposits and withdrawals are not recorded in the members accounts.
    So I have two tables for transactions and one table containing all the records associated with all the members.
    I have individual queries for each member and have fields from the transactions and complete members table extracted now, but I can't figure our a way of displaying how the member's single deposit is allocated to his dues,meals,etc.. sub accounts. If I had a way, when preparing his bill, of segregating those sub accounts, then when I receive that single amount payment, I would subdivide his payment.
    To complicate things, when I receive checks at a meeting, I use one deposit slip. That then forces me to enter each individual check received as a transaction record and, using the deposit slip code, relate all the checks to the single bank deposit.

    I developed a work breakdown structure which has an alpha code for different types of income and expense transactions. Maybe I need to refine that code and allocate more fields in both the members consolidated table and the administrative transaction table.

    I tried several searches to find out if there was an application out there which uses Access in the type of environment I need, but have not located any.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Based on your description, you need an application instead of a schema.

    Just buy any one of the low end accounting packages. In an earlier post healdm suggested Sage/TAS Books. I'd suggest Intuit's Quickbooks. Any one of these packages can do what you've described, and have been extensively tested and certified as meeting all of the appropriate standards.

    You will be far better off using one of these packages than attempting to write a package from scratch. Based on your post #3 above, you don't understand enough of GAAP to defend the results your application would produce (most people don't, this isn't any failure on your part). Without that understanding, you can't defend answers produced with a system you develop which is why you really MUST use a package so that you don't need to defend those decisions.

    Please, leave the accounting schema design to accounting professionals. Buy a package (any commercial package) and use the package so that you can concentrate on running your club. This is a far better use of your time. Both you and the club will benefit from using a standard package.

    -PatP

  5. #5
    Join Date
    Nov 2008
    Posts
    9
    Quickbooks cost $200 per application for one user, which makes that application financially prohibitive. In looking at its features, I don't feel they are that applicable to a charitable organization. But I will admit I am biased against Intuit as a result of my experience with their poor support of an upgrade.

    I wonder if I could get help on my original subject.
    I have all 55 members accounts loaded into one table. Each member has a unique member identification and I have auto id for a unique transaction. I have one table for administrative transactions and one for charities transactions. Each member is charged for dues and each meeting meal.
    Each check from members, from charities income, disbursements is scanned, the check number is recorded in the record and associated with the deposit slip number.

    Right now I am trying to figure out how to develop an acceptable bill form using reports. I started with forms, but I do not know how to produce a form or report where I could make it only display data for a single member.

    My other challenge is to link the administrative transaction table to the members account table so that when I post a members check, the details would also appear in the members account tables. I have an individual query for each member.

    Any suggestions?

    JP

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    have a parameterised query for each member
    select * from mytable where memberID = [which member id?]
    or better yet
    on you switchboard/menu you have a combo or list box which lists all members, you select the member there
    select * from mytable where memberID = forms!mymmenuform!mylistboxofmembers

    reason.. when a new member comes along y'don't have to write a new query

    Id have a single transaction table which had all payments, all income in the same table, I'd have a transcation code table which identified what the transaction was (and identify its effect on the account)...
    eg
    sale effect +
    purchase effect -
    member refund effect -
    and so on
    if you wanted to complicate matters you could flag whteher the transaction affected the sales or purchase 'ledger' or give it any other meaning you want to

    no transaction could have a negative value
    each transaction identifies the type of transaction, the date, optionally the member who its "to" or "from", I'd also include the URL/Path to the image you have scanned and store that.
    some audit info (who sanctioned the transaction and so on)

    you need to get out of the mentality that one person = one entity, one table, one query, one report, one whatever. yes fine if a member gets a report which is unique to them.... but its a generic report for all members, you just limit the data by filter

    however I would make the observation that you may need to think about going to a night school nearby to learn how to use Access, how to design db systems.. although this sites contributors will help, I suspect they have neither the time or patience to take you through the whole process.

    I'm surprised that the base accounts package is that price, in the UK the base accounts package (SAGE) is around £50, I would expect a variant of that to be available in the US for around $100

    If I were you, for the job you are doing I think you are either very brave or very stupid to consider doing what you are doing. Peace of mind on financial stuff, when you are acting as an honourary treasurer is critical. if the organisation wont spring for an accounts package, then for my peace of mind I woudl personally. becuase I don't want to hand the accounts to a third party in a mess, I don't want to risk data dissappearing, I don't want to stray to far from who ever audits your accounts comfort zone... in fact have you approached that person and agreed this course of action. I think you are building major trouble for yourself on this, and furthermore you are building it needlessly.

    either do it on paper or in an accounts package, but for your sanity (and ours) PLEASE do not try to write this system without a good knowledge of database design and whatever your chose db is

    if you want to learn about db systems there are far less complex subjects to tackle and get familiar with.
    Last edited by healdem; 12-12-08 at 16:30.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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