AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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.
