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 isnt to say that I wont 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. Lets 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 havent 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 dont 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 dont 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