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

[spydey]

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 (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 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 families is the AECG assigned, the members of each families, the address, 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, FamilyID of 29 = Smith, AssignedAdult1 = 137; People table, PersonID of 137 = John Doe. Thus John Doe is assigned to the Smith family as part of the AECG.

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, it provides me with the actual name, as found in the People table, not the PersonID.

The SQL code for this is:

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.

It needs to contain the AECG member names assigned to that family, as well as the phone numbers of the AECG members.

It needs to contain the following information about the family:

• Family Name
• 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)

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.

My problem:

I haven’t used Access for years (as previously stated). Just designing the tables and queries really made me dig back into my old school books, purchase online classes to refresh myself, study some SQL (something I have no experience in … yet (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.

Everything I get is in a columnar format, not per family, and lists the AECG members for each member of the family, which I don’t want. I just wanted it listed once per family.

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 …. ?????

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

 

[xenou]

Reports take a lot of experience and trial and error. There are no simple answers. But possibly you want to create a report that has a main report and a subreport - the main report is the Family ID (parent record) and the subreport are the people in the family (linked to the main report by the family ID).