I have a db that tracks audits done on certain companies. There is a one to many relationship between the company information and the various audits done on each company. Each audit covers two years and companies must be audited every two years. I need to create a report which tells me which companies are due for audit. The audit table includes fields for audit period begin date and end date. Any help would be greatly appreciated!!!!!
I can give you some help on this, but I need a few things quantified. Is the End Date automatically populated with the Start Date plus 2 years, since you said each audit covers 2 years, or is it left empty until the audit is actually completed? When you say that a company "must be audited every two years", is that within 2 years of the start of the last audit or the end of the last audit?
SELECT tblClients.ClientID, tblClients.ClientName, Max(tblAudits.BeginDate) AS [Last Audit Start], Max(tblAudits.EndDate) AS [Last Audit End]
FROM tblClients INNER JOIN tblAudits ON tblClients.ClientID = tblAudits.ClientID
GROUP BY tblClients.ClientID, tblClients.ClientName
HAVING (((Max(tblAudits.BeginDate))<Max([enddate])) AND ((Max(tblAudits.EndDate))<(Date()-730)));
Replace the tblClients references with your Table name for your clients and whatever you have called the fields in that table. Also, replace tblAudits references with your Table name where you store the audits. BTW, the reason for the statement "HAVING (((Max(tblAudits.BeginDate))<Max([enddate]))' is to exclude any clients with ongoing audits, which might show up because their maximum EndDate is more thsn 2 years ago.