none
MS Access help RRS feed

  • Question

  • Hello

    I want to build a access database for church members, but our church members are not based on indiviuals instead they are based on Families.

    My question if anyone could help me on how would my tables be, i would like a simple database for names, address, phone, email  birthday, relationship between family members,  no calculations .

    Any help appreciated.

    Thanks in advance.

    Younan Yalda


    Younan

    Wednesday, August 31, 2016 7:22 PM

Answers

  • Familes
    ********************
    FamilyId
    FamilyName
    Address
    City
    ...
    HomePhone
    ....

    FamilyMembers
    ********************
    FamilyMembersId
    FamilyId
    FirstName
    LastName
    CellPhone
    WorkPhone
    WorkPhoneExt
    Email 
    DOB
    Relationship    (Father, Mother, Daughter, Son, ...)
    ...   

    Now things can get even more complicated.  For instance, in many cases people have multiple e-mail addresses, you might need to break the e-mail address off into its own table.

    FamilyMembersEmails
    ********************
    EmailId
    FamilyMembersId
    Email
    EmailType     (Personal, Work, ...)

    The same can be true of phone numbers

    FamilyMembersPhoneNumbers
    ********************
    PhoneNumberId
    FamilyMembersId
    PhoneNumber
    PhoneNumberType     (Personal, Work, ...)

    It all depends on the granularity of the data you need.  No need to make things more complicated than you need, but you don't want to develop an Excel style table either by adding columns like Email1, Email2, Email3 and the likes.  You have to find the right balance for your needs.


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, August 31, 2016 9:38 PM

All replies

  • Familes
    ********************
    FamilyId
    FamilyName
    Address
    City
    ...
    HomePhone
    ....

    FamilyMembers
    ********************
    FamilyMembersId
    FamilyId
    FirstName
    LastName
    CellPhone
    WorkPhone
    WorkPhoneExt
    Email 
    DOB
    Relationship    (Father, Mother, Daughter, Son, ...)
    ...   

    Now things can get even more complicated.  For instance, in many cases people have multiple e-mail addresses, you might need to break the e-mail address off into its own table.

    FamilyMembersEmails
    ********************
    EmailId
    FamilyMembersId
    Email
    EmailType     (Personal, Work, ...)

    The same can be true of phone numbers

    FamilyMembersPhoneNumbers
    ********************
    PhoneNumberId
    FamilyMembersId
    PhoneNumber
    PhoneNumberType     (Personal, Work, ...)

    It all depends on the granularity of the data you need.  No need to make things more complicated than you need, but you don't want to develop an Excel style table either by adding columns like Email1, Email2, Email3 and the likes.  You have to find the right balance for your needs.


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, August 31, 2016 9:38 PM
  • Also, have you check any of the Access Templates?  You might be able to repurpose one of those.

    Lastly, I have no clue how good it is, but I did come across: Church Management Software Solutions  from http://gainingaccess.net/GainingAccess/FreeDownloads.aspx it may be worth your while to check out.  (I have no affiliation with them, nor have I tried out the sample - so it's a use at your own risk)


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, August 31, 2016 9:47 PM
  • Hi Younanyy,

    The Table design is already provided by the other community member.

    you can try to quick start using that Table Designs.

    also as per your requirement you can modify the tables and relationships.

    Here you had only asked about tables but if you want to do something further then you can also try to make Forms and Reports.

    you can use that forms to insert , update, view or delete the new members data. you can also save their pictures so it will be easy to recognize them.

    you can generate reports from that data. like how many people are below the 18 years old. how many people are retired. who working in a particular sector. or list all the persons who lives in particular area of the city. and lots more. everything is depends on your requirements.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 1, 2016 1:05 AM
    Moderator
  • Hello again,

    Very sorry for late answer.

    I am very greatfull for all who answered my request, i have worked with access but it was long time ago and almost forgot the the most of it. The table above is enough for a start then i will move on to forms, so that i can use the form to enter data, reports as i recall is easy its just choose the field i need and make a report. I would appreciate if  any one would guide me on how and which fields should have relationship with each other in the tables descriped above.

    Again please accept thanks and best regards.

    Younan Yalda


    Younan

    Sunday, September 4, 2016 5:05 PM
  • All foreign keys need to be related to their primary keys.

    For example FamilyMembers.FamilyID is a FK which needs to relate to Families.FamilyID (which is a PK). When you create the relation, be sure to check the box to enforce the relation.


    -Tom. Microsoft Access MVP


    Sunday, September 4, 2016 5:36 PM
  • Dear Mr. Daniel Pineault

    In your table description in your reply above you had a field named familyID and FamilyID members, what is the difference between them, i dont understand what you mean exactly with theses 2 fields.

    Thanks in advance.

    Best Regards

    Younan Yalda


    Younan

    Sunday, September 4, 2016 6:58 PM
  • Hi YounanYY,

    If I understand correctly then,

    Family Id is to identify the family uniquely from all families of church members.

    like There are 50 Families are there. so each family get the unique identification number.

    so that we can Identify that 10 number family is Mr.Jones Family.

    and FamilyId members is to identify the member of family uniquely inside the family.

    like 1st number is for Father, 2nd is for mother and 3rd for the child.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 5, 2016 4:57 AM
    Moderator
  • DEar Sirs,

    Thank you very much for your fast reply and appreciate all the help to all that who helped me with their answers.

    In these 2 tables ypu advised me, I asume that Family ID should be the PK in both tables. or should it be another field in the second table Family Members ?

    Please accept my best regards and Thanks in advance

    Younan Yalda


    Younan

    Monday, September 5, 2016 8:38 PM
  • Hi YounanYY,

    Yes , FamiyId and Family member id will be primary key in both tables.

    so that you can reference it with foreign key in another tables where you want to use it.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 6, 2016 2:42 AM
    Moderator