Query to concatenate records - newb

[bentod]

I have two tables.
One for FAMILIES that has all sorts of family specific information. This has a primary key of ID.
I have another table for INDIVIDUALS that has family member specific information. Each member has a ROLE of 1 (dad), 2 (mom), or 3 (dependents). There is a field for the FAMID that is the same as the FAMILY table ID

Families will always have a dad or a mom, however, they may or may not have both. In addition, they may not have dependents.
When a person needs to find a family, they need to know who the mom and dad are, because there could be several people with the same last names.

I need a field that concatenates the FIRSTNAME of records with a ROLE of 1 with the FIRSTNAME of records with the ROLE of 2 along with the FAMILYNAME from the FAMILY table.



I suppose this is easy for some genius out there.
Thank you

[June7]

The Individuals table has record for each individual? You want the Role1 and Role2 records to be concatenated to one row?

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

[bentod]

The Individuals table has record for each individual? You want the Role1 and Role2 records to be concatenated to one row?

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

Hey, I'm way open to suggestions.
Here's how I see it. Families have information and the people in the family have information that may not have anything to do with the family (like personal email addresses).

Unfortunately, a lot of people share last names so it would be difficult to just have a combo-box with last names only. Therefore, I want to have a field that grabs the first name of the "head of house", which I say has a ROLE of 1 and concatenate that name with the "spouse". Obviously, I would have an ampersand between the two names. I.e. Carl & Linda Smith. FIRSTNAME (ROLE 1) & FIRSTNAME (ROLE 2) FAMILYNAME.

That whole "newb" thing should tell you a lot. Is there a better way to do this?

[June7]

Okay, easier than I thought. Use multiple queries.

SELECT FamilyID, Firstname FROM Individuals WHERE Role=1;

SELECT FamilyID, Firstname From Individuals WHERE Role=2;

RowSource for the combobox
SELECT FAMID, [Role1].[Firstname] & IIf(IsNull([Role1].[Firstname]),""," and ") & [Role2].[Firstname] & " " & [Familyname] AS Parents
FROM Role1 RIGHT JOIN (Role2 RIGHT JOIN Families ON Role2.FamilyID = Families.FAMID) ON Role1.FamilyID = Families.FAMID;

All in one statement:
SELECT Families.FAMID, [Role1].[Firstname] & IIf(IsNull([Role1].[Firstname]),""," and ") & [Role2].[Firstname] & " " & [Familyname] AS Parents
FROM (SELECT Individuals.FamilyID, Individuals.Firstname, Individuals.Role FROM Individuals WHERE (((Individuals.Role)=1))) As Role1
RIGHT JOIN ((SELECT Individuals.FamilyID, Individuals.Firstname, Individuals.Role FROM Individuals WHERE (((Individuals.Role)=2))) As Role2
RIGHT JOIN Families ON Role2.FamilyID = Families.FAMID) ON Role1.FamilyID = Families.FAMID;

[bentod]

You've been so nice, I hate to be a pain... but you know I'm gonna.
Anyway, I did a find/replace to make your field/table/query names match mine. There are a few I'm unsure of. I wrote the definitions at the bottom.

I underlined the questions here and below.
...AS Parents - What is this for?
...RIGHT JOIN Families ON... - Should this be FamilyTBL?
...ID=Families.FamilyID... Since I get a an error on this, I assume

..............................After Conversion....................................
SELECT ID, FirstName FROM IndividualTBL WHERE Role=1;

SELECT ID, FirstName FROM IndividualTBL WHERE Role=2;

SELECT FamilyID, [Role1QRY].[FirstName] & IIf(IsNull([Role1QRY].[FirstName]),""," and ") & [Role2QRY].[FirstName] & " " & [FamilyLastName] AS Parents
FROM Role1QRY RIGHT JOIN (Role2QRY RIGHT JOIN Families ON Role2QRY.ID = Families.FamilyID) ON Role1QRY.ID = Families.FamilyID;
.................................................. .............................
FamilyFRM - The main form
IndividualFRM - The subform
FamilyTBL - The table for family information
IndividualTBL - The table for inidividual information
ID - FamilyTBL primary key
FamilyID - IndividualTBL number linked to FamilyTBL:ID (one to many)
IndID - IndividualTBL primary key
FamilyLastName - FamilyTBL field for the last name of the family (not necessarily the individual)
FirstName - IndividualTBL field for the first name
Role1QRY - the query to get the first name of the "father"
Role2QRY - the query to get the first name of the "mother"

[June7]

Your first post said you had a table for FAMILIES, so I assumed that was the table name. Use whatever your actual table and field names are.

PARENTS is alias name for the field constructed by expression.

View the query structure in Design view.

[bentod]

Ahhh. I see. Thank you for everything. You rock!

[bentod]

One last favor (of course, when I say, "last" it's all relative).

It isn't getting the FamilyID if there is no person with a Role=2 (a single woman).