Expert: Scottgem - 2/9/2005

Question
Windows XP and Access 2003
I'm such a novice at this, it's been years since I had Access training and I don't have a book.
I'm trying to set up a database for a church members directory. I've made a form for entering the data and a report for runing a list of the whole church. Where I'm stuck is that I'd like to be able to run a list for each seperate ministry department and I'd like to be able to input for each member, every dept he/she is a part of. I can't seem to figure out how to do this. Every member can be involved in more than one ministry dept. If I set it up so that I have a check box for the dept's in my form, as in John Smith, Men's-check, Choir-check, I get all the right information in my table, but can't query for a single department with out creating a query for each dept. Do I need to set up more than one table and create a relationship? And if so, how do I do this?
Can you help? Is there a simple solution I am overlooking? I know all the basics, and can do macros etc, but for some reason this has me stumped.

Answer
What you have here is a many to many relationship. So there are at least three tables required. Your members table, the ministries table and a join table to connect them. the Join table will look like this:

MemberMinistryID (Primary key Autonumber)
MemberID (Foreign key to member table
MinistryID (FK to Ministry table)

You then add a subform to your main form bound to the join table and linked on MemberID. On that form, place a combobox where you select the ministry. There will be a record in the join table for each ministry each member is involved in.

From there is easy to get a report grouped by ministry or for a specific ministry. You just join the tables and filter for a specific ministry.

I'm not sure how you have things setup, but your member data should probably be multiple tables as well. The Member table would just have ID info for a member, MemberID, First, Middle & Last names, maybe DOB. You would then have a MemberFamily table with Family name, address, etc. This means the member table would also have a foreign key to the Family table.

The point is that anytime you have to repeat data in a table, then you should be creating a subtable for it.

HTH
Scott<>