Access Table Question

paconnors

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

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

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.

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

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

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.

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"