Hello All,
I am trying to "successfully" create a Directory report. I was using an underlying nested query that contained tables including tblAddress (AddressID) and tblPeople (PeopleID) to show spouses at the same address on one line of the report. There is a one to many relationship between tblAddress and tblPeople. The underlying query combined the spouse's names living at the same address:
LName:[tblpeople].[LastName] &
IIf([tblpeople].[PKRelationship]=1,
IIf((SELECT dupe.LastName
FROM tblpeople as dupe
WHERE tblpeople.AddressID = dupe.AddressID and
dupe.PKRelationship=2 and tblpeople.LastName <>dupe.LastName
)<>"",
" & " & (SELECT dupe.LastName
FROM tblpeople as dupe
WHERE tblpeople.AddressID = dupe.AddressID and
dupe.PKRelationship=2 and tblpeople.LastName
<>dupe.LastName
),
"")
)
So this says, given the same AddressID, look at the husband's (PKRelationship =1) last name and see if the wife's (PKRelationship=2) last name is the same. If they aren't, combine them, otherwise report one name. (I use this for significant others too.) This worked great until I realized I wasn't getting the wife's info in my Directory report because I lost her PeopleID when I combined names. So I decided to combine the names in my Directory report instead of the underlying query.
My Directory report shows the LastName and Address in the LastName header; at this point I'm grouping on LastName. My report's record source is qryDirectoryFinalbyName. The phone numbers and email addresses show up in the detail section. For the LastName field's control source, I modified the above to refer to query qryNameCombinedLast:
LName:[LastName] &
IIf([PKRelationship]=1,
IIf((SELECT dupe.LastName
FROM tblPeople as dupe
WHERE AddressID = dupe.AddressID and dupe.PKRelationship=2 and
LastName <>dupe.LastName
)<>"",
" & " & (SELECT dupe.LastName
FROM tblPeople as dupe
WHERE AddressID = dupe.AddressID and dupe.PKRelationship=2
and LastName <>dupe.LastName
),
"")
)
My underlying query works fine. I have this referenced in the LastName field text box of my LastName header (grouped on LastName, sort on LastName). If I use a DLOOKUP ("LName", "qryNameCombinedLast"), I only get the first record's last name from my tblPeople--no one else. When I use a similar query for FName, the family lists for EVERY address (AddressID) in my report. Do you know what I'm doing wrong?
Thank you for reviewing this long explanation.
I am trying to "successfully" create a Directory report. I was using an underlying nested query that contained tables including tblAddress (AddressID) and tblPeople (PeopleID) to show spouses at the same address on one line of the report. There is a one to many relationship between tblAddress and tblPeople. The underlying query combined the spouse's names living at the same address:
LName:[tblpeople].[LastName] &
IIf([tblpeople].[PKRelationship]=1,
IIf((SELECT dupe.LastName
FROM tblpeople as dupe
WHERE tblpeople.AddressID = dupe.AddressID and
dupe.PKRelationship=2 and tblpeople.LastName <>dupe.LastName
)<>"",
" & " & (SELECT dupe.LastName
FROM tblpeople as dupe
WHERE tblpeople.AddressID = dupe.AddressID and
dupe.PKRelationship=2 and tblpeople.LastName
<>dupe.LastName
),
"")
)
So this says, given the same AddressID, look at the husband's (PKRelationship =1) last name and see if the wife's (PKRelationship=2) last name is the same. If they aren't, combine them, otherwise report one name. (I use this for significant others too.) This worked great until I realized I wasn't getting the wife's info in my Directory report because I lost her PeopleID when I combined names. So I decided to combine the names in my Directory report instead of the underlying query.
My Directory report shows the LastName and Address in the LastName header; at this point I'm grouping on LastName. My report's record source is qryDirectoryFinalbyName. The phone numbers and email addresses show up in the detail section. For the LastName field's control source, I modified the above to refer to query qryNameCombinedLast:
LName:[LastName] &
IIf([PKRelationship]=1,
IIf((SELECT dupe.LastName
FROM tblPeople as dupe
WHERE AddressID = dupe.AddressID and dupe.PKRelationship=2 and
LastName <>dupe.LastName
)<>"",
" & " & (SELECT dupe.LastName
FROM tblPeople as dupe
WHERE AddressID = dupe.AddressID and dupe.PKRelationship=2
and LastName <>dupe.LastName
),
"")
)
My underlying query works fine. I have this referenced in the LastName field text box of my LastName header (grouped on LastName, sort on LastName). If I use a DLOOKUP ("LName", "qryNameCombinedLast"), I only get the first record's last name from my tblPeople--no one else. When I use a similar query for FName, the family lists for EVERY address (AddressID) in my report. Do you know what I'm doing wrong?
Thank you for reviewing this long explanation.
