Query Help

[atd]

I'm trying to figure out a query similar to the planned appt tracker list. The difference is that I'm subtotalling the planned appointments, with specific Unscheduled statuses, for each public health site. What I'm missing is how to exclude the planned appointments that are already scheduled. I'm sure it's simple, but I'm new at this. Here's what I have so far:

SELECT site.Description, COUNT(appointment.PatNum) as Patients, SUM(appointment.IsHygiene) as HygAppt FROM appointment
LEFT JOIN patient ON appointment.PatNum=patient.PatNum
LEFT JOIN site ON patient.SiteNum=site.SiteNum
WHERE appointment.AptStatus=6 AND patient.ClinicNum=1 AND (appointment.UnSchedStatus=0 OR appointment.UnSchedStatus=420 OR appointment.UnSchedStatus=428)
GROUP BY patient.SiteNum

If anyone can point me in the right direction I'd appreciate it!

[jordansparks]

appointment.NextAptNum contains a foreign key to the planned appt. So...
AND NOT EXISTS (SELECT * FROM appointment aptSched WHERE aptSched.NextAptNum=appointment.AptNum)

[atd]

Perfect, thanks for your help.