Relationships based on ID Number

[Sherry1913]

I used to design and develop databases until I retired in 2006, so I'm more than a little rusty. I am trying to create a Membership database for my church that will include several tables. I'm trying to figure out how to relate one table to another based on the Automatically Generated ID number. For instance, the Membership table will include the member's name, automatically generated Id Number and demographic info. Then there needs to be a table that will show information about when the member joined and how they joined (by letter or by baptism). If they joined by letter, another table will be needed for information on the previous church they belonged to so that our church can have the letter transferred to it.(I'm thinking I could use a Yes/No field here, that if checked YES (meaning they joined by letter), and it would open a new table where the information for the previous church's demographic info could be entered, then a report generated so that our secretary could send a letter of transfer to the previous church. If they joined by baptism, there will be a table to give the baptism date and place. I don't know any programming languages like SQL or C++, just simple straight text, but all the tables in this database need to be related by the member's ID number. Can anyone help me remember how to do this? Thanks in advance.

My tables might look something like this: tblMembers, tblLetter, tblBaptism, tblFamily, tblPosition (Pastor, Music Director, Board Member, Usher, etc) The reason I'm adding the tbl Family is because not all of our married members belong to the same church. This would be where I would add the Member's spouse's name as well as any children.

[orange]

Richard Rost has a youtube series related to building a database for Associations. Clubs, churches...etc.
Depending on how much time and effort you intend to spend "refreshing" this video might be helpful to you.

[xps35]

I am not sure you need all these tables. You only need an extra table when there is a one to many relationship. For example, in this case I think there is a one to one relationship between member and the way he/she joined. Then you might as well put that information in one table (member).

[Micron]

Forgive me if you know most of this already.

Automatically Generated ID number

Is this the autonumber you're referring to? If yes, you are not going to use that as meaningful info, correct? It is only providing uniqueness to each record and for relating to other related tables.

When they joined should go in tblMembers.

A related table for joining methods - yes, because there can be more than one possible value for the "joining" type. Otherwise you'd need a field in tblMembers for each type - not good.

No to the yes/no field to mark as joined by letter. That will come from tblJoinTypes.

Yes to a table of Churches - as a lookup table (not the same as a lookup field). Not sure what you mean by church demographics but I suspect those fields should be part of the church records. IF you care to know about each of the churches a member may have belonged to at one time or another, you'll need a table to handle that many-to-many relationship with junction table.

Probably would need tblBaptisms as well.

As for tblFamily - I don't think so but not sure of its exact purpose. If the family members are church members they probably should go into tblMembers. If they are not members, then I might rethink/rename tblMembers so that it encompasses family members as well. Then perhaps a field to denote a record as a member or not. That might be best as a date field. If it's Null, they are not. If there is a date, it flags them as a member as well as the date they joined. IMO, Yes/No fields are seldom better than date fields for things like this.

Not sure what Pastor/usher is either. If they can only have one role at any time, it can be in tblMembers. You might want tblRoles for this and keep the PK value from roles in tblMembers. Again, if you want their role history or if they can hold more than one role at a time you will need another junction table for that.