Find Family Members Not Listed as Patients

If you have family members listed in your database who were never formally added as patients—perhaps they were entered as guarantors or emergency contacts—this report helps you clean up your records. Running an open dental family not patients query allows you to identify these individuals so you can decide whether to update their status or remove redundant entries.

The Query

This query identifies individuals who are listed as a Guarantor for at least one patient but are not themselves marked as an active patient (PatStatus 0).

/* Set the status to look for - 0 is Active Patient */
SET @ActiveStatus = 0;

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.Email
FROM patient p
WHERE p.PatStatus != @ActiveStatus
AND p.PatNum IN (
    SELECT DISTINCT Guarantor 
    FROM patient 
    WHERE Guarantor != PatNum
);

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below the text box.

Understanding the Results

• PatNum: The unique identification number assigned to the individual in your database.
• LName / FName: The last and first name of the person.
• HmPhone: The home phone number on file, which can help you verify if this is a duplicate or a contact person.
• Email: The email address on file, useful for cross-referencing with your patient communication software.

How to Customize

If you want to narrow your search to specific types of non-patients, you can modify the WHERE clause.

• Filter by PatStatus: If you only want to see people marked as "NonPatient" (PatStatus 1) rather than "Inactive" (PatStatus 2) or "Archived" (PatStatus 3), change the line WHERE p.PatStatus != @ActiveStatus to:
  WHERE p.PatStatus = 1
• Exclude specific names: If you have a test patient or a dummy account you want to ignore, add this to the end of the query:
  AND p.LName NOT LIKE 'Test%'

Variations

Find Non-Patients with an Email Address

If you are planning a marketing campaign and want to see which non-patient guarantors have valid email addresses, use this variation:

SELECT PatNum, LName, FName, Email
FROM patient
WHERE PatStatus != 0
AND Email != ''
AND PatNum IN (SELECT DISTINCT Guarantor FROM patient WHERE Guarantor != PatNum);

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.