quote:

---

So, the record only gets returned if it doesn't exist in both tables.

---

select  a.*
from    <tableWithAllRows> a
Left outer join  <tableWithMissingRows> m
    on m.<all columns to correlate on> = a.<all columns to correlate on>
where  m.<any correlated column> is NULL

Like this:

select d.*
FROM AmpPha_DB d
left outer join AmpPha_Patients p
ON     d.Visit = p.EXAMDATE
AND    d.Birth = p.BIRTHDATE
AND    d.Last = p.LASTNAME
and    d.[od amp] = Round(p.[AMPDX],2)
and    d.[od pha] = Round(p.[PHDX],2)
and    d.[os amp] = Round(p.[AMPSX],2)
and    d.[os pha] = Round(p.[PHSX],2)

where p.examdate is null

ORDER BY d.Last

select min(sourceTbl) as sourceTbl
       ,d.Visit
       ,d.Birth
       ,d.Last
       ,d.[od amp]
       ,d.[od pha]
       ,d.[os amp]
       ,d.[os pha]
from   (
       select 'AmpPha_DB' as sourceTbl
              ,d.Visit
              ,d.Birth
              ,d.Last
              ,d.[od amp]
              ,d.[od pha]
              ,d.[os amp]
              ,d.[os pha]
       from   AmpPha_DB
       UNION ALL
       select 'AmpPha_Patients'
              ,d.Visit = p.EXAMDATE
              ,p.BIRTHDATE
              ,p.LASTNAME
              ,Round(p.[AMPDX],2)
              ,Round(p.[PHDX],2)
              ,Round(p.[AMPSX],2)
              ,Round(p.[PHSX],2)
       from   AmpPha_Patients
       ) d
group by d.Visit
       ,d.Birth
       ,d.Last
       ,d.[od amp]
       ,d.[od pha]
       ,d.[os amp]
       ,d.[os pha]
having count(*) = 1
order by 2,3,4,1