Results 1 to 9 of 9
  1. #1
    Spydey is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    10

    Generating a report - issues, confusion, road block .....

    Hey there everyone!! First post (and its a doozy!!!).



    Ok, so just to give a quick disclaimer, I am fairly new to using Access.

    I took a college class on it like back in either 2011 or 2013. I really haven't had to use since then and so I apologize in advance for any simple/silly mistakes, follow-up questions, etc.

    Although I may be new to this, that isn’t to say that I won’t catch on quickly.

    Also, thank you in advance for taking the time to read this as my posts tend to be somewhat of a …. novel, and for assisting me. I very much appreciate it.

    ----------------------------------------

    My scenario:

    I am preparing an emergency contact system/plan for my community.

    It is fairly simple.

    The premise is that no more and no less than two adults (18+ years of age) are assigned to a family (never their own). There can be 0, 1, or 2 youth (ages 14+) assigned to those 2 adults too, to assist with the assigned family. This partnership of adults, and possibly youth, are responsible for their assigned family. Let’s call this partnership (the assigned adults and possibly assigned youth) the AECG (Assigned Emergency Contact Group).

    In case of an emergency, the AECG is to contact their assigned families to determine if everything is ok, if they need assistance, etc.

    The report is to provide who comprises each AECG (adults & youth), to which family is the AECG assigned, the family's address, the members of the family and their corresponding telephone, email, DOB, Age, Gender, etc.

    I have two tables currently:
    • Families
    • People

    *** FYI, due to the nature of the sensitive personal information found in the tables, I cannot share them. Sorry. I know it would perhaps simplify things, but I have to ensure privacy is maintained. ***

    The Families table consists of the following fields:

    • FamilyID (Primary Key) (AutoNumber)
    • Zone (Number)
    • FamilyName (Short Text)
    • Address1 (Short Text)
    • Address2 (Short Text)
    • City (Short Text)
    • State (Short Text)
    • Zip (Short Text)
    • AssignedAdult1 (Number)
    • AssignedAdult2 (Number)
    • AssignedYouth1 (Number)
    • AssignedYouth2 (Number)
    • Notes (Long Text)


    The People table consists of the following fields:

    • PersonID (Primary Key) (AutoNumber)
    • FamilyID (Number)
    • Zone (Number)
    • LastName (Short Text)
    • FirstName (Short Text)
    • Gender (Short Text)
    • DOB (Date/Time)
    • Age (Number)
    • Phone (Short Text)
    • Email (Short Text)
    • Notes (Long Text)

    In the Families table, for the AssignedAdult/Youth fields, I have input the PersonID (from the People table) of the corresponding person assigned to that family as the AECG.

    e.g. Families table, The FamilyName where FamilyID = 29 is: Smith, AssignedAdult1 = 137; People table, Person First & Last Name where PersonID = 137 is: John Doe. Thus John Doe is assigned to the Smith family as part of the AECG. Hopefully that makes sense.

    I have a query set up which provides me with a list of the families and their assigned AECG. It gives me:
    FamilyID, Zone (Families table), FamilyName, Address1, Address2, City, State, Zip, AssignedAdult1, AssignedAdult2, AssignedYouth1, AssignedYouth2

    For the AECG in the query, it provides me with the actual name, as found in the People table, not the PersonID.

    The SQL code for this is:

    Code:
    SELECT Families.FamilyID, Families.Zone, Families.FamilyName, Families.Address1, Families.Address2, Families.City, Families.State, Families.Zip, [AssignedAdult1].[FirstName] & " " & [AssignedAdult1].[LastName] AS [Assigned Adult 1], [AssignedAdult2].[FirstName] & " " & [AssignedAdult2].[LastName] AS [Assigned Adult 2], [AssignedYouth1].[FirstName] & " " & [AssignedYouth2].[LastName] AS [Assigned Youth 1], [AssignedYouth2].[FirstName] & " " & [AssignedYouth2].[LastName] AS [Assigned Youth 2]
    FROM (((Families INNER JOIN People AS AssignedAdult1 ON Families.AssignedAdult1 = AssignedAdult1.PersonID) INNER JOIN People AS AssignedAdult2 ON Families.AssignedAdult2 = AssignedAdult2.PersonID) LEFT JOIN People AS AssignedYouth1 ON Families.AssignedYouth1 = AssignedYouth1.PersonID) LEFT JOIN People AS AssignedYouth2 ON Families.AssignedYouth2 = AssignedYouth2.PersonID
    ORDER BY Families.Zone, Families.FamilyID;
    -----------------------------

    My desired outcome:

    I need to create a report where every two pages represents a single Family.

    I say every two pages because a family could be 1 person up to 9 people.

    I figured 2 pages should cover up to 9 people. It would be printed front and back so that physically it is one page.

    Unless there is a way where we could make each "Family Record" print separately from the other "family records" ... but that isn't super urgent or critical.

    It needs to contain the following information:

    • Family Name
    • AECG Member Names (listed on separate lines)
    • AECG Member phone number(s) (listed next to the corresponding AECG member)
    • Family Address (Address1, Address2, city, state, zip) (do not need to be concatenated)
    • Each member of the family
      • First Name & Last Name
      • Gender
      • DOB
      • Age
      • Phone
      • Email
      • Notes (i.e. medical information as deemed necessary)


    ------------------------------------------

    My problem:

    I haven’t used Access for years (as previously stated). Just designing the tables, inputting the info manually from multiple inconsistent PDFs, and setting up the queries really made me dig back into my old school books, purchase online classes to refresh myself (haven't finished them yet but they have been very helpful thus far), study some SQL (something I have no experience in … yet ... but have quite enjoyed thus far (I find it quite intriguing)).

    Designing the reports is going to again push me past my current knowledge and experience, which is fine. I just don’t know where to being.

    My issue is that I cannot seem to get the report to come out the correct way. I am stuck. I have tried a number of things but they don’t seem/feel like they are leading me down the path to my desired outcome.

    If I use the two tables then I get all the data I want, except that the AECG members come over as just their PersonID numbers, not their names.

    If I use the query (see code above) which DOES bring over the AECG names, it doesn't have all the data points for the individual family members that I need.

    If I update the query to include the individual family members, then the AECG members get duplicated for each member of the family and it shows in my report as such.

    It is like get so close one way, but am lacking one detail. So I find a way to get that detail to work, but then am lacking the original parts that I previously had.

    I can't seem to get them to play nice and do what I want ..... hahahahaha

    I am not sure how I am going to list each family member and only have the AECG members listed once. Do I need to implement some sort of grouping/sorting?

    I am not sure how to get it to output is one family per report record. Again, grouping/sorting?

    It almost seems like I need a form instead of a report, but as I understand it, forms are for input to the tables, and reports are output from the tables/queries. Right?

    Would designating a FamilyMemberID to each person perhaps simplify things? I think that might be over-complicating things and really isn't needed …. ?????

    I really feel like I am so close to accomplishing my goal but feel like it is just out of reach because I am missing some key aspect to designing the query/report to get my desired outcome.


    Any help, pointers, assistance, ideas, suggestions, etc., that can help is greatly and deeply appreciated!!

    Thank you again for reading my post (aka Novel).

    Take care!


    -Spydey

    P.S. I am using Access 2016

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Family table doesn't need any children.
    you want another tFamChildren table to hold infinite children in the family.

    all details in tPerson table.

  3. #3
    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
    When developing/designing/testing any database, many people will use "fictitious names" and create some "test scenarios". This "anonymizes" the data so it can be shared, edited, deleted etc without loss of real data, nor dissemination of personal/private info.

    Names like Pork Pig, Donald Duck, General Purpose, Sheeza Payne let you work through development and debugging sessions and/or sharing.

    If your record DOB, you can always calculate Age, so I would not store Age in a table.

    Good luck with your project.

  4. #4
    Spydey is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    10
    Quote Originally Posted by ranman256 View Post
    Family table doesn't need any children.
    you want another tFamChildren table to hold infinite children in the family.

    all details in tPerson table.
    Yes, I have no children or people in the Families table.

    All details are in the People table.
    I can indicate which family a person belongs to by inputting the corresponding FamilyID into the Person's record, in the FamilyID field within the People table.

    Sorry if perhaps I don't know the correct jargon yet .....

    -Spydey

  5. #5
    Spydey is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    10
    Quote Originally Posted by orange View Post
    When developing/designing/testing any database, many people will use "fictitious names" and create some "test scenarios". This "anonymizes" the data so it can be shared, edited, deleted etc without loss of real data, nor dissemination of personal/private info.

    Names like Pork Pig, Donald Duck, General Purpose, Sheeza Payne let you work through development and debugging sessions and/or sharing.

    If your record DOB, you can always calculate Age, so I would not store Age in a table.

    Good luck with your project.
    I guess that I cold copy the two tables, minimize the number of records in each, and then input fictitious data, as you mentioned above.

    Regarding the Age, yes, I actually have an update query designed to update the age based upon the DOB. But are you stating that I could have the field auto-calculate the age based upon the DOB?

    -Spydey

  6. #6
    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
    But are you stating that I could have the field auto-calculate the age based upon the DOB?
    Not exactly. I'm saying use DOB in your table. If/When you have to deal with Age, you can calculate it via a query based on DOB.
    I'm saying don't use Age as a field in a table.

  7. #7
    Spydey is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    10
    Quote Originally Posted by orange View Post
    Not exactly. I'm saying use DOB in your table. If/When you have to deal with Age, you can calculate it via a query based on DOB.
    I'm saying don't use Age as a field in a table.
    Ok, I think I got ya.

    I think that I did part of what you said already, but then did the other part you recommended not to do.

    The age field is based off of a query using the DOB to calculate the age.

    You're saying, use the query based off of the DOB to calculate the age, but ONLY when you need the age for a given output (report, data sheet, etc). Don't have the age stored in the table as a field.....

    Is that right?

    -Spydey

  8. #8
    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

  9. #9
    Spydey is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    10
    So I had a thought today, that might make things more simple for me.

    What if instead of designing a report (which is turning out to be quite time consuming because I have no experience for the level of report I need), I design a mail merge in WORD????

    That could be my "report" base. I would then need a data sheet for the source data. I have done plenty of mail merges before from data sheets (excel) so I am not too concerned there.

    I think I already have the majority of the items for the data sheet.

    However, thinking through this, due to how word's mail merge works (if I am not mistaken), all of the data points for one record ("letter") need to be in the same row.

    So my concern is, how do I generate a query which will capture all of the family members and out put them to a single row?

    The number of family members varies, from 1 on up.

    ----------

    I am enjoying this process of learning, exploring, testing, etc. But it seems like every time I come up with a possible solution to one problem, another one presents its ugly head, hahahahaha ....

    Any thoughts, ideas, suggestions?

    Thanks in advance!!

    -Spydey

    Also, should I post my new idea/issue in a new thread to perhaps capture more attention to it and possibly more assistance?

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

Similar Threads

  1. Advice for Road Project Database
    By Drumstix in forum Database Design
    Replies: 5
    Last Post: 11-10-2014, 05:44 PM
  2. Using an Address Block Module for a report
    By mcgeester in forum Reports
    Replies: 11
    Last Post: 08-04-2014, 01:12 PM
  3. Replies: 5
    Last Post: 03-13-2012, 08:24 PM
  4. Block IF End IF Confusion
    By Phred in forum Programming
    Replies: 11
    Last Post: 03-10-2012, 02:18 PM
  5. The last Road
    By imintrouble in forum Reports
    Replies: 6
    Last Post: 11-04-2011, 03:47 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