Code:
SELECT DISTINCTROW "Aged Consult" AS Alert, Scheduler_Sheet.ConsultID, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Urgency, Scheduler_Sheet.Program, Scheduler_Sheet.Clinic, Scheduler_Sheet.LastAction, Scheduler_Sheet.DateofAction, Scheduler_Sheet.AppointmentDate, Scheduler_Sheet.RNComment, Scheduler_Sheet.SchedulerComment, Scheduler_Sheet.RNActionNeeded, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.ConsultNumber
FROM tblEmployeeFiles INNER JOIN (Scheduler_Sheet INNER JOIN tblSchedulerClinics ON Scheduler_Sheet.Clinic = tblSchedulerClinics.Clinic) ON tblEmployeeFiles.AlertTeam = tblSchedulerClinics.AlertTeam
WHERE (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate)<Date()-2) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate) Is Null) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Received") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Requested") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser]))
UNION SELECT DISTINCTROW "Red Zone, Attempt to Close" AS Alert, Scheduler_Sheet.ConsultID, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Urgency, Scheduler_Sheet.Program, Scheduler_Sheet.Clinic, Scheduler_Sheet.LastAction, Scheduler_Sheet.DateofAction, Scheduler_Sheet.AppointmentDate, Scheduler_Sheet.RNComment, Scheduler_Sheet.SchedulerComment, Scheduler_Sheet.RNActionNeeded, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.ConsultNumber
FROM tblEmployeeFiles INNER JOIN (Scheduler_Sheet INNER JOIN tblSchedulerClinics ON Scheduler_Sheet.Clinic = tblSchedulerClinics.Clinic) ON tblEmployeeFiles.AlertTeam = tblSchedulerClinics.AlertTeam
WHERE (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate)<Date()-2) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate) Is Null) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Requested") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Received") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser]));