AllExperts > Using MS Access 
Search      
Using MS Access
Volunteer
Answers to thousands of questions
 Home · More Using MS Access Questions · Answer Library  · Encyclopedia ·
More Using MS Access Answers
Question Library

Ask a question about Using MS Access
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Scottgem
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

 
   

You are here:  Experts > Computing/Technology > Business Software > Using MS Access > Setting up a database

Using MS Access - Setting up a database


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<>  

Add to this Answer   Ask a Question


 
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.