Results 1 to 6 of 6
  1. #1
    janthony is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    15

    Can I create a "Directory" report combining family members?

    So, I've tried to do research on this. I don't even know what to search for, so I've had no luck. I thought I needed a mail merge, but after looking into that a little bit, I don't think that's what I need.




    What I've got is a non-profit contribution tracking database designed as such:

    Click image for larger version. 

Name:	Table and relationships.png 
Views:	11 
Size:	6.6 KB 
ID:	19526

    I would like to be able to create a directory out of the members, where members are kept together. But, I would like:
    • the address to show only once
    • married couples to show up together (i.e. Mr. _____ & Mrs. ______ ____________)



    Is this possible to do? With the current database design?
    What is the best way of going about it?
    Have I just designed this database poorly and need to re-do something?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Which address? How to determine which address should be the 'family' address?

    How do you know which of the members are a 'couple'? What if each goes by a different last name?

    And with so many blended families, how do you determine which is the 'family' name?
    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
    janthony is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    15
    Well, this is kind of where it fuzzy.

    It's assumed there will only be two members within the same family, which are married.
    It's also assumed that both members will have the same address, unless they want to file separately.
    The FAM_DESC is for internal administrative use to distinguish between families with the same last name (i.e. Johnson 1, Johnson 2). It's essentially just to categorize members of the same family together.
    The Family table is what establishes a "family", so it wouldn't matter if they have the same last name. Although I do see your point now, that you can't always assume they will have the same last name. So a syntax of <FNAME> + <FNAME> + <LNAME> wouldn't work.

    Looking at this, you brought up a lot of good issues I hadn't even though of. Is there a better way to set this up so that I can get what I want?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So a 'family' is only the couple - no kids?

    Couples aren't always married.

    If there is only one address it should be in the Family table. But if there is only the two people, why have two tables? This is one time when multiple similar fields in one table might be justified. Each record in Members table could have fields for both members' names
    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 XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell readers in detail and plain English WHAT exactly do you want to track? There may be options.
    I don't think your assumptions will work well in practice, but you know the details and readers don't.

  6. #6
    janthony is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    15
    I was able to figure it out after considering the issues June7 brought up. I hadn't thought about the design properly in those terms.

    What I did was added address fields to the family table. This address would be the master address for the directory. To reduce redundancy, I created code, that when the family is selected/changed on create/modify member form, it would automatically populate the address fields based on the family address. That way, if for example, starting a new family from scratch, you would start by creating the family, then creating the members for the family and selecting the family they belong too.

    This gave me what I needed which was something to the effect...

    <The [FAM_NAME] Family>
    <[MEM_FNAME] [MEM_LNAME]> it would list out both two members of the same family, if applicable; last names wouldn't matter as it would include both members first and last name
    <[FAM_ADDRESS]>

    Thank you both for helping me to formulate this much better in my head. In hindsight, I could've created an even much better design, but I'm sort of patching this up to be workable for the time being.

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

Similar Threads

  1. All family members with one address
    By Grams in forum Access
    Replies: 5
    Last Post: 08-25-2014, 05:51 PM
  2. Replies: 3
    Last Post: 04-29-2014, 04:49 PM
  3. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  4. Replies: 1
    Last Post: 08-23-2012, 08:32 AM
  5. Replies: 1
    Last Post: 05-23-2011, 08:07 AM

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