I need to populate a report using a query that displays data in this way:
First field: ParenLastName, ParentFirstName ParentMid; ParentLastName, ParentFirstName, ParentMid
Second field: ChildLastName, ChildFirstName, ChildMid
Third field: ChildCaseNumber
These two fields (child name and case number) could have several children, but all of them have the same Parent's.
To complicate this a bit more, each of these records are related to one of three types of agreement. It is not uncommon for each family group to have all three agreements.
I am able to get one parent name to display on the report, but the child name is duplicated. The multiple agreement part appears to be fine.
Just to make it more fun, I need to have the parent's names seperated with a semi-colon as one string
( Adams, John J; Adams, Mary ).
Here is what I have:
SELECT DISTINCT Agreement.agreementID, Agreement.agreementType, Agreement.agreementAmountApproved, Agreement.agreementSentDate, Agreement.agreementReceivedDate, qryPersonLastFirstMiddleName.Name AS FamilyName, Left([FamilyName],1) AS FirstLetter, qryChildFirstMiddleLastName.PersonName AS ChildName, Format([Placement]![plAACaseNumber] & [Placement]![plChildAACasePersonLetter],">") AS CasePL, Placement.plDisruptionDate
FROM ((Agreement INNER JOIN (Placement INNER JOIN PlacementAgreement ON Placement.placementID = PlacementAgreement.placementID) ON Agreement.agreementID = PlacementAgreement.agreementID) INNER JOIN qryPersonLastFirstMiddleName ON Placement.recruitmentID = qryPersonLastFirstMiddleName.recruitmentID) INNER JOIN qryChildFirstMiddleLastName ON Placement.biologicalID = qryChildFirstMiddleLastName.biologicalID
WHERE (((Agreement.agreementSentDate) Is Not Null) AND ((Placement.plDisruptionDate) Is Null))
ORDER BY qryPersonLastFirstMiddleName.Name;
This is what the report data looks like:
Adams, Mary <parent> Baby Boy Jones Sissy Jones Sissy Jones Baby Boy Jones <child> 123C 123D 123D 123C <caseNumber>
This is what it should look like:
Adams, Mary; Adams, John J <parent> Baby Boy Jones Sissy Jones <child> 123C 123D <caseNumber>
First field: ParenLastName, ParentFirstName ParentMid; ParentLastName, ParentFirstName, ParentMid
Second field: ChildLastName, ChildFirstName, ChildMid
Third field: ChildCaseNumber
These two fields (child name and case number) could have several children, but all of them have the same Parent's.
To complicate this a bit more, each of these records are related to one of three types of agreement. It is not uncommon for each family group to have all three agreements.
I am able to get one parent name to display on the report, but the child name is duplicated. The multiple agreement part appears to be fine.
Just to make it more fun, I need to have the parent's names seperated with a semi-colon as one string
( Adams, John J; Adams, Mary ).
Here is what I have:
SELECT DISTINCT Agreement.agreementID, Agreement.agreementType, Agreement.agreementAmountApproved, Agreement.agreementSentDate, Agreement.agreementReceivedDate, qryPersonLastFirstMiddleName.Name AS FamilyName, Left([FamilyName],1) AS FirstLetter, qryChildFirstMiddleLastName.PersonName AS ChildName, Format([Placement]![plAACaseNumber] & [Placement]![plChildAACasePersonLetter],">") AS CasePL, Placement.plDisruptionDate
FROM ((Agreement INNER JOIN (Placement INNER JOIN PlacementAgreement ON Placement.placementID = PlacementAgreement.placementID) ON Agreement.agreementID = PlacementAgreement.agreementID) INNER JOIN qryPersonLastFirstMiddleName ON Placement.recruitmentID = qryPersonLastFirstMiddleName.recruitmentID) INNER JOIN qryChildFirstMiddleLastName ON Placement.biologicalID = qryChildFirstMiddleLastName.biologicalID
WHERE (((Agreement.agreementSentDate) Is Not Null) AND ((Placement.plDisruptionDate) Is Null))
ORDER BY qryPersonLastFirstMiddleName.Name;
This is what the report data looks like:
Adams, Mary <parent> Baby Boy Jones Sissy Jones Sissy Jones Baby Boy Jones <child> 123C 123D 123D 123C <caseNumber>
This is what it should look like:
Adams, Mary; Adams, John J <parent> Baby Boy Jones Sissy Jones <child> 123C 123D <caseNumber>
