Need help designing a database for a club

[tsuchong]

Hi all,



I need some advice on how to go about design a database for my club. Here are some details:

1. I want to have a MASTER TABLE listing all the members of the club. This table will have the details of each member e.g. name, address, email, etc.

2. The club runs many activities throughout the year. I want to have a separate table showing members who attended an activity (e.g. ACTIVITY A).

Q1: For Table ACTIVITY A, how do i design it so that i can just select from a drop down list? The drop down list should show the members entered into MASTER TABLE. When selected, their full info should appear in Table ACTIVITY A.

Q2: Once their name appears in ACTIVITY A, I want an indication to appear in MASTER TABLE that the person has attended Activity A - e.g. the name of the activity in one of the columns.

Hope the above is clear.

Thanks

[pbaldy]

The typical normalized design would involve your master table, with info on members, an activity table, with info on each activity, and a junction table to represent the many-to-many relationship between the first two tables. A member can attend many activities and and activity can be attended by many members. That table would have fields for member ID and activity ID.

[tsuchong]

But when i lookup a member, how can i know which activities has he/she attended?

[pbaldy]

One method often used would be a form bound to the member table and a subform bound to the junction table. Master/child links would keep the subform displaying the activities for whatever member was currently displayed.

[tsuchong]

Hey thanks alot.

I've got it to work!

[pbaldy]

Happy to help, and welcome to the site by the way!