Query tweak to get singles - newb

[bentod]

I have an SQL query to get the names of the heads of the house. Sometimes there will be two (male and female), sometimes there will be one (either single male or single female).

The male's name is gathered in "Role1QRY".
The female's name is gathered in "Role2QRY".
The result should look like: George & Gracie Burns or Stevie Wonder or Linda Carter

June7 was nice enough to help me, but the following query has a few issues:
1) It pulls the man's name (from Role1QRY) and always adds the ampersand (&) even if there isn't a female (Stevie & Wonder).
2) It doesn't give the IndID for single females (the field is blank).

I've tried to make adjustments, but it is apparent I'm over my head.

SELECT Role1QRY.IndID, [Role1QRY].[FirstName] & IIf(IsNull([Role1QRY].[FirstName]),""," & ") & [Role2QRY].[FirstName] & " " & [FamilyLastName] AS FiledAs
FROM Role1QRY RIGHT JOIN (Role2QRY RIGHT JOIN FamilyTBL ON Role2QRY.[IndID] = FamilyTBL.ID) ON Role1QRY.[IndID] = FamilyTBL.ID;

Note:
There are two tables: FamilyTBL and IndividualTBL. There is a main form (FamilyFRM) and a subform (IndividualFRM).

Key:
FamilyFRM - The main form
IndividualFRM - The subform
FamilyTBL - The table for family information
IndividualTBL - The table for inidividual information
ID - FamilyTBL primary key
IndID - IndividualTBL number linked to FamilyTBL:ID (one to many)
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 "male"
Role2QRY - the query to get the first name of the "female"

[bentod]

OK, I tinkered until got the first problem figured out like this:

SELECT Role1QRY.FamilyID, [Role1QRY].[FirstName] & IIf(IsNull([Role1QRY].[FirstName]) OR IsNull([Role2QRY].[FirstName]), "" , " & ") & [Role2QRY].[FirstName] & " " & [FamilyLastName] AS FiledAs

However, I don't understand the RIGHT JOIN well enough to solve the IndID problem (item 2 above).
Everyone always has an IndID but if the Role1QRY doesn't have a value then Role2QRY joins with nothing. How would I add an IIf to use the IndID when Role1QRY was Nill?

Thank you.

[ssanfu]

I created a spreadsheet with the example names you used and created a formula to get the names like you want them. Then I substituted the query names back into the SQL.
Not sure (UNTESTED), but try this:

SELECT Role1QRY.IndID,[Role1QRY].[FirstName] & IF(ISNULL([Role2QRY].[FirstName]),"",IF(ISNULL([Role1QRY].[FirstName]),""," & ")) & [Role2QRY].[FirstName] & " " & [FamilyLastName] AS FiledAs 
FROM Role1QRY RIGHT JOIN (Role2QRY RIGHT JOIN FamilyTBL ON Role2QRY.[IndID] = FamilyTBL.ID) ON Role1QRY.[IndID] = FamilyTBL.ID;

[bentod]

You have a teeny problem: your "IF" needs to be "IIF". Other than that, the SELECT work perfectly.

Unfortunately, it doesn't address the second problem I have with the FROM section.

Thank you. That sounds like a pretty interesting spreadsheet.

[ssanfu]

You have a teeny problem: your "IF" needs to be "IIF". Other than that, the SELECT work perfectly.

Thank you. That sounds like a pretty interesting spreadsheet.

I hate the differences between Access & Excel - sorry about the IIF(). Spreadsheet was just male name female name and family name. Then create a formula to get the name the way you wanted.

Unfortunately, it doesn't address the second problem I have with the FROM section.

Without your tables/queries, it is difficult (for me) the envision the solution. Wold you care to post your dB (without any sensitive data)?

[bentod]

This is very preliminary.

[ssanfu]

So I looked at the dB.... your tables are a little confusing.

I modified your queries. I think you were using the family ID from the wrong table. I created a new form to be able to see the results of the query.

Hope this is what you want...

[bentod]

Oh yes! I don't really care about the Male or Female ID's, but hey, that's gravy. Thank you. This will do the trick.