Advice on Table Structure

[timeh]

hi, I am currently writing a database for a local squash club.
I've tried to design it a few times but i seem to come unstuck along the way with the same problems.

The database stores member details such as name, address, telephone number etc. I put these into a table called tbl-members, which has the following fields;

tbl-members:
memberID (autonumber, primary key)
member name
member address
member telephone

I then have a seperate table to hold the contract details for each member;

tbl-fees:
contractID (autonumber, primary key)
memberID (lookup from members table)
general fee
visitor fee
total fees

The user needs to be able to store details about any visitors that members bring to the club, including a fee for each visitor. This is where my problem arises. So far, my attempts involved creating another 'visitors' table like this

tbl-visitors:
visitorID
memberID (lookup from members table)
visitor address
visitor fee

Now, the 'visitor fee' part is causing me problems. What i did was i created a form with subform, so that 'visitors details' could be added for members, i.e. one member could have more than one visitor. This works fine and the correct visitors are related to the correct members. However, say a member has two visitors, and the visitors fees are £10 and £5 respectively. I would want £15 (the total of the two) to be put into the "visitor fee" field of the "tblfees" table. I made the table into a form called "frmfees", but all formulas i have tried in the "visitor fees" field don't seem to work.

Any advice or possiblities would be greatly appreciated, or suggestions a on a better approach. I say this every post, but i am not very experienced in access....still, and there may be simpler or more effective ways around the problem

thanks in advance

 

[timeh]

thanks

Pat, thanks a lot for the advice. I'm going to try to do what you suggested.
First off, i'm not sure what your mean when you say "add a flag"
Do you mean a field from which you can select if the 'person' is a member or visitor?

Yes - i have abbreviated the field lists for name and address, i have a few seperate fields for each, e.g. forname and surname.

I've renamed tblfees to "tblcontracts" as it does hold yearly fees. I'm going to impliment a query and mailmerge to send a letter to members who's contracts are due to be renewed, i think i know how to do that.

You suggested that after i put the visitors into the members table i would need another table to hold details on their visits, i've done this, creating "tblfees"

Yes, members are responsibe for paying visitors fees
There are no restrictions at present concerning visitors

Fees are paid immediately

I'm not totally sure how having all the visitors in the members table will work, any ideas(which i'm sure you have) would be very helpful. I'm new to access and all advice is very much appreciated.