Trying to create a database to keep track of my Church's members. I created a Men, Women, and Children table and query. I want to be able to create a query for men and their spouse, next a query that will generate men,spouse and children as a family. I have no idea how to setup the realtionships to make this work. preview my table/queries and please help me, i been working on this for weeks and still cant figure out the relationship. Am I missing a table? please let me know if you want more screenshoots and info, I'm very new to Access

How about setting up one table for everyone, then setting up a table to hold the relationships between them. Something Like:

tblMembers:

pkMemberID - Autonumber, pk
FirstName
LastName
fkFamilyID - Number - will be related to tblFamilies
fkFamRelTypeID - Number- will be related to
tblFamilyMemberRelationshipTypes

tblFamilies
pkFamilyID - Autonumber, pk

tblFamilyMemberRelationshipTypes
pkFamRelTypeID - Autonumber, pk
FamRelType - ex: Husband, Wife, Child

In a form or report you can have different labels for things, like a textbox to display the father's last name and "family", etc....

Trying to create a database to keep track of my Church's members. I created a Men, Women, and Children table and query. I want to be able to create a query for men and their spouse, next a query that will generate men,spouse and children as a family. I have no idea how to setup the realtionships to make this work. preview my table/queries and please help me, i been working on this for weeks and still cant figure out the relationship. Am I missing a table? please let me know if you want more screenshoots and info, I'm very new to Access


Welcome to the forum,

Perhaps if you upload a copy of the database with no real records in it then we can check it out for you.

I'm sorry, what do you mean by no record?

Thank you, I will try this out. It sounds a lot more reasonable than what I have thats for sure.

Post your DB with dummy information instead of real infor for privacy's sake.

After reading the replies, I decided to create

Tables:
· tblFamily
o pkFamily ID

· tblRelationship type
o pkRelationship ID
o Relationship type (value list, row source:father,mother,child,etc.)

· tblMembers
o pkMember ID
o Last Name
o First Name
o *Family ID – related to tblFamily
o *Relationship type ID – related to tblRelationship type

Queries:
· Members query
o Includes everything from all three tables

Now my problem is, when working with the query I can’t get the Family ID to generate anything. I believe that the Relationship ID is generated through the Relationship type. Am I missing something in the tblFamily? Is there a better way to setup a members query?

Have a look at this link (http://allenbrowne.com/ser-06.html)to maybe get some ideas on solving your problem

Here's another link that may give you some ideas.
http://www.databaseanswers.org/data_models/genealogy/facts.htm

related link
http://www.databaseanswers.org/data_models/genealogy/index.htm

Thank you, the links helped a lot, I am making progess but now I can't figure out how to group all the members that belong to the same family. My idea was to assign all members that belong to the same family, the same familyID but I don't think this is possible since familyID is my PK. What should I try doing instead?