How to take multiple records from the same "group" and populate them to a single row in a table ????

[Spydey]

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.

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

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.

[Spydey]

Ok, so getting the email and phone numbers over was quite simple. I don't know why I didn't see it below. I already had the tables related correctly. I just needed to bring down each item from the corresponding record. I feel kind of silly that is was so obvious!! hahaha

Now on to the bigger issue: How to get each person record, related to the same family, to populate into a single row for that family.

I currently have 144 families in my families table & 294 people in my people table. After designing the query, I should have 144 records, one per family, and each record should have all members (and their corresponding data (i.e. email, phone, age, etc)) of that family in the same row.

Maybe I am missing a FamilyMemberID per person???? Would that simplify things?

-Spydey

[Spydey]

Ok, so I adjusted a few naming conventions for the fields, etc, but it is all the same data.

Here is my updated code so far:

SELECT Families.FamilyID, Families.Zone, Families.FamilyName, Families.Address1, Families.Address2, Families.City, Families.State, Families.Zip, [AA1].[FirstName] & " " & [AA1].[LastName] AS [AA 1], AA1.Phone AS [AA 1 Phone], AA1.Email AS [AA 1 Email], [AA2].[FirstName] & " " & [AA2].[LastName] AS [AA 2], AA2.Phone AS [AA 2 Phone], AA2.Email AS [AA 2 Email], [AY1].[FirstName] & " " & [AY1].[LastName] AS [AY 1], AY1.Phone AS [AY 1 Phone], AY1.Email AS [AY 1 Email], [AY2].[FirstName] & " " & [AY2].[LastName] AS [AY 2], AY2.Phone AS [AY 2 Phone], AY2.Email AS [AY 2 Email]

FROM (((Families INNER JOIN People AS AA1 ON Families.AssignedElder1 = AA1.PersonID) INNER JOIN People AS AA2 ON Families.AssignedElder2 = AA2.PersonID) LEFT JOIN People AS AY1 ON Families.AssignedYouth1 = AY1.PersonID) LEFT JOIN People AS AY2 ON Families.AssignedYouth2 = AY2.PersonID;

Also, I have not been typing out the SQL code. I have simply been using the Design View then reviewing the SQL code.

-Spydey

[June7]

Only 2 youth per family? The query should work, until a family has more than 2 youth.

You have two-way linking of these tables. This should not be done.

Review http://allenbrowne.com/func-concat.html

[Spydey]

Only 2 youth per family? The query should work, until a family has more than 2 youth.

So let me clarify. The family could potentially be comprised of 1 person (an adult) up to say many people, with a mix of adults and youth (children).

Each family has 2 adults (not members of the family) assigned to the family as their emergency contacts. Let's call this the AECG (Assigned Emergency Contact Group). Apart from the 2 adults in the AECG, there could be up to an additional 2 youth (14 years of age +) in the AECG, but not members of the assigned family.

Example: Family #1 has 2 people: 2 adults and one child. Their AECG is comprised of Adult #1 (person comes from family #116), Adult #2 (person comes from family # 93), Youth # 1 (person comes from family #3), no youth #2.

Does that make sense?

You have two-way linking of these tables. This should not be done.

So would you explain a bit more about this, please? I am fairly new to all of this and any clarification you could provide will greatly help me. Thanks!

Review http://allenbrowne.com/func-concat.html

Thanks for the link!! I will have to review it when I have a moment.

-Spydey

[June7]

Okay, that makes more sense and then the links do seem appropriate.

[Spydey]

Okay, that makes more sense and then the links do seem appropriate.

Fantastic!! I was worried that although visually it looked correct, I had set it up incorrectly.

Thanks for the clarification

Now I need to figure out how to get the rest of the family members to show up ...

I think that for now perhaps I need to assign each of them a FamilyMemberID.

But couldn't I simply associate the combination of FamilyID and PersonID as a unique FamilyID Eric?

Now how do I pull that in to my query and how do I have it account for all family members?

[June7]

Don't see need for FamilyMemberID field.

A query should be able to retrieve all related data for a report. Use Sorting & Grouping features.

If you want all family members in one line in report, that requires code. Review the link provided.

Use form/subform for data entry.