Hey there ladies and gents. New to the forum here .... only my second thread.
Also quite new to Access. Still trying learn a bunch. Thanks in advance for the assistance, patience, understanding, pointers, suggestions, input, corrections, etc. Any kind of instruction helps!!
--------------------------
I have two tables:
Families & People
Families is comprised 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)
*** FYI, the AssignedAdult/Youth are the PeopleID from the People table. There will always be two adults assigned to a family (never members of the family), but there may be 0, 1, or 2 youth assigned to the same family (never members of the family). ***
People is comprised of the following fields:
- PersonID (Primary Key) (AutoNumber)
- FamilyID (Number)
- Zone (Number)
- LastName (Short Text)
- FirstName (Short Text)
- Gender (Short Text)
- DOB (Date/Time)
- Phone (Short Text)
- Email (Short Text)
- Notes (Long Text)
I need a query where it will output each field from the Families table (except the FamilyID), list the name, telephone, and email of the AssignedAdults/Youth (not their PeopleID), and output each field from the People table (except the PersonID and FamilyID), all into a single row. This is to be exported to excel for use in a Word Mail Merge.
I believe I have some of the query already. It provides me with the Families fields and includes the names of the AssignedAdults/Youth. I can't seem to bring over their telephone and emails though.
The other major issue I am having and don't really know which way to go or where to even begin is getting the people details over to the same row.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;
The dilemma is that a family could have multiple people in it. It could range from 1 to x. Currently I think the largest family is something like 7 people. But I shouldn't limit it. It should be dynamic and adjust to how ever many people are in a family.
If I bring over the people table fields to the query, it generates a single record per person of that family. I need them all on the same row for the Word Mail Merge to function properly.
I hope that all makes sense.
Any ideas, suggestions, recommendations, etc?
Maybe I don't have my tables set up correctly for my desired outcome?
I am not really sure where to go from here.
Thanks again in advance to any and everyone.
-Spydey
P.S. If you are going to recommend a report straight out of Access, I was trying that (see first thread/post about that here: Generating a Report). I just was not getting the results I wanted. I am fairly closer now using that approach but I think that for sake of time (would like to have this completed by Sunday) being able to export all of the family & people data to a single row in excel and mail merge it will give me the results I am looking for sooner.