Complicated Query returning double results

M

MStadnik

Hi all,
I have another, much harder, question. I'm trying to create a query that
will feed into a report that generates emergency contact forms for each
participant. The query is currently returning two records for each
participant - I think because for each participant there are two family
caregiver contacts requested in the query. Is there a way to get both family
caregiver contacts but not get doubles on the participant info? Copied below
is the sequel for the query - I know that this is a complicated query,
pulling info from multiple tables but I'm hoping that there is a fairly
straightforward solution. Thanks for your help.

SELECT Participants.FirstName AS Participants_FirstName, Participants.MI,
Participants.LastName AS Participants_LastName, Participants.Photo,
Participants.StreetAddress1, Participants.StreetAddress2, Participants.City
AS Participants_City, Participants.State AS Participants_State,
Participants.Zip AS Participants_Zip, Participants.County,
Participants.HomePhone, Participants.OtherPhone, Participants.DateofBirth,
Participants.SSN, Participants.LivingWill, Participants.DateofAdmission,
FundingCaseMgmt.VNAAffiliation, FundingCaseMgmt.VNAPhone,
FundingCaseMgmt.CaseManagementContact, FundingCaseMgmt.CaseManagementPhone,
CaregiverFamily.CaregiverType, CaregiverFamily.FirstName AS
CaregiverFamily_FirstName, CaregiverFamily.LastName AS
CaregiverFamily_LastName, CaregiverFamily.Relationship,
CaregiverFamily.Street1 AS CaregiverFamily_Street1, CaregiverFamily.Street2
AS CaregiverFamily_Street2, CaregiverFamily.City AS CaregiverFamily_City,
CaregiverFamily.State AS CaregiverFamily_State, CaregiverFamily.Zip AS
CaregiverFamily_Zip, CaregiverFamily.PPhone, CaregiverFamily.BPhone,
CaregiverFamily.OPhone, CaregiverFamily.Guardian, CaregiverFamily.POA,
CaregiverFamily.DHPOA, Medical.PhysicianType, Medical.FirstName AS
Medical_FirstName, Medical.LastName AS Medical_LastName, Medical.Street1 AS
Medical_Street1, Medical.Street2 AS Medical_Street2, Medical.City AS
Medical_City, Medical.State AS Medical_State, Medical.Zip AS Medical_Zip,
Medical.Phone, Medical.Fax, Medical.HealthConcernsSpecialNeedsDescription,
Medical.AllergiesDescription, Participants.DateofRecordEntry,
FundingCaseMgmt.MedicaidNumber, FundingCaseMgmt.MedicareNumber,
FundingCaseMgmt.OtherInsurance, FundingCaseMgmt.OtherInsuranceBilling
FROM ((Participants INNER JOIN FundingCaseMgmt ON
Participants.[ParticipantID] = FundingCaseMgmt.[ParticipantID]) INNER JOIN
Medical ON Participants.[ParticipantID] = Medical.[ParticipantID]) INNER JOIN
CaregiverFamily ON Participants.[ParticipantID] =
CaregiverFamily.[ParticipantID]
WHERE (((CaregiverFamily.CaregiverType)="primary caregiver") AND
((Medical.PhysicianType)="attending")) OR
(((CaregiverFamily.CaregiverType)="family member"));
 
K

Klatuu

You should probably use a subreport to show the caregivers. There really
isn't any reasonable way to flatten out your data into one row. A subreport
would be much easier.

Just leave the caregiver table out of your query. For the sub report,
create a query that includes the ParticipantID and use the ParticipantID
fields from both tables in the Link Master Field(s) Link Child Field(s)
properties of the subreport control.
--
Dave Hargis, Microsoft Access MVP


MStadnik said:
Hi all,
I have another, much harder, question. I'm trying to create a query that
will feed into a report that generates emergency contact forms for each
participant. The query is currently returning two records for each
participant - I think because for each participant there are two family
caregiver contacts requested in the query. Is there a way to get both family
caregiver contacts but not get doubles on the participant info? Copied below
is the sequel for the query - I know that this is a complicated query,
pulling info from multiple tables but I'm hoping that there is a fairly
straightforward solution. Thanks for your help.

SELECT Participants.FirstName AS Participants_FirstName, Participants.MI,
Participants.LastName AS Participants_LastName, Participants.Photo,
Participants.StreetAddress1, Participants.StreetAddress2, Participants.City
AS Participants_City, Participants.State AS Participants_State,
Participants.Zip AS Participants_Zip, Participants.County,
Participants.HomePhone, Participants.OtherPhone, Participants.DateofBirth,
Participants.SSN, Participants.LivingWill, Participants.DateofAdmission,
FundingCaseMgmt.VNAAffiliation, FundingCaseMgmt.VNAPhone,
FundingCaseMgmt.CaseManagementContact, FundingCaseMgmt.CaseManagementPhone,
CaregiverFamily.CaregiverType, CaregiverFamily.FirstName AS
CaregiverFamily_FirstName, CaregiverFamily.LastName AS
CaregiverFamily_LastName, CaregiverFamily.Relationship,
CaregiverFamily.Street1 AS CaregiverFamily_Street1, CaregiverFamily.Street2
AS CaregiverFamily_Street2, CaregiverFamily.City AS CaregiverFamily_City,
CaregiverFamily.State AS CaregiverFamily_State, CaregiverFamily.Zip AS
CaregiverFamily_Zip, CaregiverFamily.PPhone, CaregiverFamily.BPhone,
CaregiverFamily.OPhone, CaregiverFamily.Guardian, CaregiverFamily.POA,
CaregiverFamily.DHPOA, Medical.PhysicianType, Medical.FirstName AS
Medical_FirstName, Medical.LastName AS Medical_LastName, Medical.Street1 AS
Medical_Street1, Medical.Street2 AS Medical_Street2, Medical.City AS
Medical_City, Medical.State AS Medical_State, Medical.Zip AS Medical_Zip,
Medical.Phone, Medical.Fax, Medical.HealthConcernsSpecialNeedsDescription,
Medical.AllergiesDescription, Participants.DateofRecordEntry,
FundingCaseMgmt.MedicaidNumber, FundingCaseMgmt.MedicareNumber,
FundingCaseMgmt.OtherInsurance, FundingCaseMgmt.OtherInsuranceBilling
FROM ((Participants INNER JOIN FundingCaseMgmt ON
Participants.[ParticipantID] = FundingCaseMgmt.[ParticipantID]) INNER JOIN
Medical ON Participants.[ParticipantID] = Medical.[ParticipantID]) INNER JOIN
CaregiverFamily ON Participants.[ParticipantID] =
CaregiverFamily.[ParticipantID]
WHERE (((CaregiverFamily.CaregiverType)="primary caregiver") AND
((Medical.PhysicianType)="attending")) OR
(((CaregiverFamily.CaregiverType)="family member"));
Click to expand...
 
M

MStadnik

Thank you - I hadn't thought of that but it makes perfect sense. I'll see if
I can figure out how to add the subreport as I work through the remainder of
creating the primary report.

Maureen

Klatuu said:
You should probably use a subreport to show the caregivers. There really
isn't any reasonable way to flatten out your data into one row. A subreport
would be much easier.

Just leave the caregiver table out of your query. For the sub report,
create a query that includes the ParticipantID and use the ParticipantID
fields from both tables in the Link Master Field(s) Link Child Field(s)
properties of the subreport control.
--
Dave Hargis, Microsoft Access MVP


MStadnik said:
Hi all,
I have another, much harder, question. I'm trying to create a query that
will feed into a report that generates emergency contact forms for each
participant. The query is currently returning two records for each
participant - I think because for each participant there are two family
caregiver contacts requested in the query. Is there a way to get both family
caregiver contacts but not get doubles on the participant info? Copied below
is the sequel for the query - I know that this is a complicated query,
pulling info from multiple tables but I'm hoping that there is a fairly
straightforward solution. Thanks for your help.

SELECT Participants.FirstName AS Participants_FirstName, Participants.MI,
Participants.LastName AS Participants_LastName, Participants.Photo,
Participants.StreetAddress1, Participants.StreetAddress2, Participants.City
AS Participants_City, Participants.State AS Participants_State,
Participants.Zip AS Participants_Zip, Participants.County,
Participants.HomePhone, Participants.OtherPhone, Participants.DateofBirth,
Participants.SSN, Participants.LivingWill, Participants.DateofAdmission,
FundingCaseMgmt.VNAAffiliation, FundingCaseMgmt.VNAPhone,
FundingCaseMgmt.CaseManagementContact, FundingCaseMgmt.CaseManagementPhone,
CaregiverFamily.CaregiverType, CaregiverFamily.FirstName AS
CaregiverFamily_FirstName, CaregiverFamily.LastName AS
CaregiverFamily_LastName, CaregiverFamily.Relationship,
CaregiverFamily.Street1 AS CaregiverFamily_Street1, CaregiverFamily.Street2
AS CaregiverFamily_Street2, CaregiverFamily.City AS CaregiverFamily_City,
CaregiverFamily.State AS CaregiverFamily_State, CaregiverFamily.Zip AS
CaregiverFamily_Zip, CaregiverFamily.PPhone, CaregiverFamily.BPhone,
CaregiverFamily.OPhone, CaregiverFamily.Guardian, CaregiverFamily.POA,
CaregiverFamily.DHPOA, Medical.PhysicianType, Medical.FirstName AS
Medical_FirstName, Medical.LastName AS Medical_LastName, Medical.Street1 AS
Medical_Street1, Medical.Street2 AS Medical_Street2, Medical.City AS
Medical_City, Medical.State AS Medical_State, Medical.Zip AS Medical_Zip,
Medical.Phone, Medical.Fax, Medical.HealthConcernsSpecialNeedsDescription,
Medical.AllergiesDescription, Participants.DateofRecordEntry,
FundingCaseMgmt.MedicaidNumber, FundingCaseMgmt.MedicareNumber,
FundingCaseMgmt.OtherInsurance, FundingCaseMgmt.OtherInsuranceBilling
FROM ((Participants INNER JOIN FundingCaseMgmt ON
Participants.[ParticipantID] = FundingCaseMgmt.[ParticipantID]) INNER JOIN
Medical ON Participants.[ParticipantID] = Medical.[ParticipantID]) INNER JOIN
CaregiverFamily ON Participants.[ParticipantID] =
CaregiverFamily.[ParticipantID]
WHERE (((CaregiverFamily.CaregiverType)="primary caregiver") AND
((Medical.PhysicianType)="attending")) OR
(((CaregiverFamily.CaregiverType)="family member"));
Click to expand...
Click to expand...
 
Top