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

spydey

Active Member
Joined
Sep 19, 2017
Messages
313
Office Version
  1. 2013
Platform
  1. Windows
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:

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.

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
 
Last edited:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,812
Members
416,342
Latest member
BlueDevil12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top