I have two tables. Clients and CltDue (cltdue contains projects for the client)
I would like to query clients that were made active between two dates and have projects for a specific year. But... here is the part that I do not know how to accomplish, I need to also show that they do not have a project created for this year as well.
So I will list all of the projects for the clients in say, 2012. But in the same report I need to know if they do not have a project already created in 2012. So the client info will show up but the project fields will be blank.
Here is the query for showing projects for 2012.
SELECT Clients.ID, Clients.Maractdate, Clients.Cltnum, Clients.CltEng, Clients.Engdept, Clients.Engdesc, Clients.Cltname, Clients.Cltoff, Employee.Emplname, Employee.Empfname, CltDue.CDDue, CltDue.CDID, CltDue.CDEventDesc, CltDue.CDDescription
FROM (Clients LEFT JOIN CltDue ON Clients.ID = CltDue.CDCltID) INNER JOIN Employee ON Clients.Engcltptr = Employee.ID
WHERE (((Clients.Maractdate) Between @BeginDate And @EndDate) AND ((Clients.Engstatus)=0) AND ((CltDue.Deleted)=0) AND ((Year([CltDue].[CDDue]))=@Year20xx));