Results 1 to 7 of 7
  1. #1
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9

    Question How to design family, person, event tables to hold birthday, anniversary and church event dates

    I am a relative newbie to creating Access databases - mostly for my own use. I don't often use VBA.

    Please help! I am designing a church database.
    I plan to have a family table, a person table and an events table.
    I want to be able to report on all of the events for our church by year or by month - birthdays, anniversaries
    and church events - sorted by month and day
    . I plan to have a birthday field in the person table and add records to the events table based on the person key. I know I could also add a church record to the person table and add church events also using the person key, although that seems a little contrived.

    Question 1: I don't want to store the year for birthdays and anniversaries: they will occur on the same date every year.
    My church event dates will change depending on the year, so I will need to be able to select those by year.
    What is the best way to design my tables - especially the date fields or queries for those? Do I need separate table for church events? If so, how would I merge them.
    Question 2: Where should the anniversary field go? If I put it in the family table, how do I get it into the events table, since the events table will be based on person key, not family key? If I put it in the person table, it is tied to an individual instead of a family.

    I'm wide open for any suggestions - more tables, a make-table. Thank you so much for any help you can give me.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    But perhaps will want to calculate elapsed years (age, years married, etc) and will need year component for that. Month and day can be extracted from full date. Never hurts to have more info.

    Anniversary of what?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Quote Originally Posted by June7 View Post
    But perhaps will want to calculate elapsed years (age, years married, etc) and will need year component for that. Month and day can be extracted from full date. Never hurts to have more info.

    Anniversary of what?
    Wedding anniversary. We will rarely have the birth or marriage year. (Most probably won't want
    people to know their age.) The dates will only be used for acknowledgement - sending cards, putting it on the church calendar, etc. I don't mind storing the year, but most of the time, it will default to the date of entry, so it will be worthless.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't want to maintain year then choices are to have month and day in separate fields or save the combined mon/day as a text field. Either way, will have to build a full date with calculations to match with date in current year, something like:

    [MoDayField] & "/" & Year(Date())

    Or

    DateSerial(Year(Date()), [MonField], [DayField])

    However, leap year (2/29/year) dates cause a complication. The first expression will build an invalid date of 2/29/2019 because it is just concatenating text. The second will return a date of 3/1/2019.

    So this date will always be an issue when you want to plot recurring dates on current year calendar.

    Might store wedding anniversary with each individual, after all individuals get married, not families.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas from this Family Tree data model and the related materials.
    I'm sure it will serve as a great reference.

    There may be other models for example at Barry Williams' site.
    Good luck with your project.

  6. #6
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Thank you everybody for your help. Sorry to be so long in updating. It was helpful checking out the family tree data model. I did finally decide to store the date as MoDay field and just calculate the year. And I added a Couples table for storing the anniversary.

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I don't know how the privacy laws are in your part of the world, but in Europe such a database would require that you can show that all data are protected and everyone has given the consent to keep the data in your database. You'll need to make clear for which purposes you will use the data .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-06-2015, 07:57 AM
  2. Replies: 4
    Last Post: 11-04-2015, 05:44 AM
  3. Replies: 12
    Last Post: 03-16-2015, 04:57 PM
  4. Replies: 7
    Last Post: 05-08-2014, 10:34 AM
  5. Inclusive dates for event
    By jon6035 in forum Queries
    Replies: 2
    Last Post: 10-01-2011, 06:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums