Referencing other tables

[simon4amiee]

Hi guys,

The code below bring back the results I want however.....

SELECT dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode,
Count(dbo_MF_PATIENT.HEYNo) AS CountOfHEYNo
FROM dbo_MF_PATIENT
GROUP BY dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode
HAVING (((Count(dbo_MF_PATIENT.HEYNo)) > 1));


My issue is I want to run this bit of code but only if the dbo_MF_PATIENT.MFPatientID appears in any of the 3 tables below:

dbo_ED_ATTENDANCE, dbP_APPOINTMENT, dbo_IP_ADMISSION

Suppose im unsure on the joining because there is only ever one patient in the dbo_MF_PATIENT table but they could appear dozens of times in any of the other 3 tables.

 

[plog]

2 options off the top of my head:

1. Make a subquery that UNIONS the PatientID values from those 3 together, then make another subquery using that UNION query to get the DISTINCT PatientID values. Lastly, you INNER JOIN that second subquery to the query you posted.

2. In the query you have, individually LEFT JOIN each of those 3 tables ON the PatientID fields then set up a calculated field to see if it is NULL in all 3 of those tables. Then if not null anywhere you allow the record in, if not exclude.

 

[arnelgp]

You can try this:

SELECT dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode,
Count(dbo_MF_PATIENT.HEYNo) AS CountOfHEYNo
FROM dbo_MF_PATIENT
WHERE
(dbo_MF_PATIENT.MFPatientID IN (SELECT MFPatientID FROM dbo_ED_ATTENDANCE)
OR
dbo_MF_PATIENT.MFPatientID IN (SELECT MFPatientID FROM dbP_APPOINTMENT)
OR
dbo_MF_PATIENT.MFPatientID IN (SELECT MFPatientID FROM dbo_IP_ADMISSION) )
GROUP BY dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode
HAVING (((Count(dbo_MF_PATIENT.HEYNo)) > 1));

 

[Ranman256]

you could build a function, pass it the PatientID,
this will open the query if all 3 tbls are true

if IsPatientInAll3(3424) then docmd.openquery "qsMyQuery"

Public Function IsPatientInAll3(ByVal pvPatID) As Boolean
on error resume next
IsPatientInAll3 = DLookup("[PatID]", table1, "[PatID]=" & pvPatID) = pvPatID And _
                  DLookup("[PatID]", table2, "[PatID]=" & pvPatID) = pvPatID And _
                  DLookup("[PatID]", table3, "[PatID]=" & pvPatID) = pvPatID
End Function