Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: need advice on best way to do a query

    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!!!!!

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    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?

    TD

  3. #3
    Join Date
    Dec 2004
    Posts
    3

    help with query

    The end date must be user defined because an audit may cover more or less than the two years.

    The next audit should (in a perfect world) be set up two years after the end date of the last audit.

    ex> Audit 1 - period begin: January 2001
    period end: December 2002

    next audit should be performed in January of 2005 with a period begin of January 2003 and period end of December 2004.

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Here is some SQL code that should do the trick.

    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.

    Hope this does the trick.

    TD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •