Access DB Help

I have an Access database which has 3 tables, 1 for members, 1 for committees, and 1 for positions.

How do I create a relationship that will collect data from all 3 tables.

I am new to access and trying to figure out how to create these relationships so they work and allow me to enter the data into the db.

A member may or may not belong to a committee or they may belong to many committees. If they do belong to a committee they will hold a position.

I have attached the db if it will help.
attendance.accdb
LVL 2
Mark WoodNetwork AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Missus Miss_SellaneusCommented:
Don't have Access installed so I can't look at the DB, but if I understand your structure you should have a fourth table to tie them together with 3 fields, memberID, positionID and committeeID. You could include other fields such as joindate and exitdate to keep a history.
Mark WoodNetwork AdministratorAuthor Commented:
I can create the table .. no problem. not sure how to do the relationship correctly. (what to what)
Missus Miss_SellaneusCommented:
Each of the three tables you described will have an ID field and the 4th table ties them all together.

So, say your 3 main tables are committee, position and member.

example structures:
committee (committeeID, committee_name,committee_purpose)
position (positionID, position_name)
member (memberID, member_name)
memberhistory (memberID, committeeID, positionID, start_date, end_date)

Each record in the memberhistory table shows one member's position in one committee.

so:
memberhistory.memberID = member.memberID
memberhistory.committeeID = committee.committeeID
memberhistory.positionID=position.positionID
PatHartmanCommented:
You have a number of problems with the Members List table that need to be corrected.  I see at least three repeating groups that should be removed and kept as separate tables.
1. Parents - this might be better named - guardians.  You should also allow more than two and you probably want to keep address and email as well as phone for these people.
2. Contacts - same problem as guardians.
3. There are a number of fields that look like they might be committee or class names.  They should be kept in a separate table so if you add new committees or classes, you don't have to modify the application design.
4. Why is every column prefixed with "A_"?  That doesn't add anything and it just increases the characters you will need to type before you get intellisense to finish the name for you.  
5. The MembersList table should also include a "family" identifier.  I would suggest using the PK of the person you consider to be the head of the household as the familyID.  This will allow you an easy way to pull up related people and also to control mailing since you would generally only want to send one letter to a household.  This would make the MembersList table self-referencing since one of its foreign keys would point back to the PK of a different record.  So, mom's, Jonny's, and suzi's FamilyID would be dad's MemberID.

Regarding your question.  Does every committee have exactly the same positions or might some committees have additional or fewer positions?  Depending on the answer to that, you might need an additional table named tblCommitteePositions to define which positions are valid for a particular committee.  Then the junction table would be:
CommitteeID (primary key fld1 - FK to tblCommittees)
PositionID (primary key fld2 - FK to tblPositions)
MemberID (primary key fld3 - FK to tblMembersList)
StartDate (primary key fld4)
EndDate
Or
CommitteePositionID (primary key fld1 - FK to tblCommitteePositions)
MemberID (primary key fld2 - FK to tblMembersList)
StartDate (primary key fld3)
EndDate

In general -
1. Table/field/object names should consist ONLY of letters and numbers and the underscore.  They should not include spaces or special characters.  My preference for object naming is CamelCase (no separators, capitalize first letter of each word).  Others prefer the_underscore (use the underscore to separate words).  Both schemes have their merits but be consistent where possible.
2. You will find it helpful to either prefix your table names - tblClasses, tblClassrooms, etc or your queries - qActiveMembers, qDeaconsList, etc.  I prefix both but one will suffice.  This makes it easier when looking at a query to determine if the join is between tables or queries and will help you with testing and debugging.
3. It is poor practice to name all your primary keys "ID".  Use a descriptive, unique name to make it easier to identify foreign keys (FK should normally be the same name as the PK they point to).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WoodNetwork AdministratorAuthor Commented:
Thnk You
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.