Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Access Table Question

Options
  • 07-04-2009 1:24pm
    #1
    Registered Users Posts: 1,641 ✭✭✭


    Hi There,

    working on a table in access for a membership for a club, i have all the colloums set up for name, address, etc, now at the end i have the following three colloums Membership fee, Amount Due, And balance due, how do i get access to subtract amount due from membership fee, to give me the balance due.

    Thanks

    Pa

    Its Access 2007


Comments

  • Registered Users Posts: 2,781 ✭✭✭amen


    not really an Access person but you need to look at your table design

    The account/payment information should be in a seperate table.
    Your model won't allow for multiple payments


  • Registered Users Posts: 197 ✭✭cracker


    You shouldn't really have a calculated field in your table. You can add a calculated field to your forms,reports, queries etc but the tables should just contain the raw values.


  • Registered Users Posts: 1,641 ✭✭✭paconnors


    cracker wrote: »
    You shouldn't really have a calculated field in your table. You can add a calculated field to your forms,reports, queries etc but the tables should just contain the raw values.

    Ok I understand that but how would I be able to get the database to pull records that contain arrears


  • Registered Users Posts: 197 ✭✭cracker


    There are loads of ways.

    If you are using a form to display the information and the amountdue and MembershipFee are displayed on the form then add a text box to the form and set the control source to

    =[MemberShipFee]-[AmountDue]

    Or you can write a vba function to return the value and set the control source to this, overkill in your case I think

    If it is from a query then just
    select [MemberShipFee]-[AmountDue] from membership table


  • Registered Users Posts: 1,641 ✭✭✭paconnors


    Thanks for all the help guys but i'm still stuck i forgot to mention some member pay their fees in instalments so i need to be able to pull up a query where the database looks for partly paid subs and gives of the name me the member and their remaining balance.


  • Advertisement
  • Registered Users Posts: 218 ✭✭Tillotson


    I'm gonna feel sick if I this is homework or something.
    I don't have Access in front of me but this is most of what you need to do. If I remember properly...
    Create 3 tables:

    Members
    Member_no Autonumber Primary Key
    Name Text
    Address Text
    Credit_Lim Number
    Balance Number

    Fees
    Fee_no Autonumber Primary Key
    Member_no Autonumber Foreign Key
    Fee_date Date
    Ammount Number

    Payments
    Payment_no Autonumber Primary Key
    Fee_no Autonumber Foreign Key
    Payment_Ammount Number
    Payment_date Date

    Go into the relationship tab and drag primary key member number to corrisponding foreign key entry in the fee table. Repeat with Fee_no.
    Checkboxes enforce referential integrity and cascade update should be checked.

    Go create > forms > more forms > forum wizard and create a form for each table.

    Next bit I'm a bit fuzzy on:
    Is there an event called "on new", or something similar
    Attach a macro to this event on fees form.
    The macro updates balance, so
    Action: Update (?)
    Arguement: balance = balance + [forms]![fees]![ammount]
    Similary a macro on the payments form
    Action: Update
    Arguement: balance = balance - [forms]![payment]![payment]

    To check the for members who have more than their credit limit outstanding create an sql statment like "select * from members where balance > cred_lim"


Advertisement