Results 1 to 2 of 2

Thread: Query Problem

  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: Query Problem

    I have two tables. One to record business details and one to record collections we have made from those businesses.

    I am trying to write a query that will return the last collection date (and business details) of those businesses: -

    a. whose last collection date was equal to or greater than a week old.
    b. and that HAVE NOT HAD a collection within the last week.

    Below is how far i have got but this query just returns the last collection date for each business that has a collection associated with it that is either a week old or older regardless of whether they have had a collection in the last week or not.

    I'm trying to get it to just return those companies that have not had a collection for a week or more.

    SELECT Max(tblCollections.LastCollectionDate) AS MaxOfLastCollectionDate, tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency
    FROM tblBusiness INNER JOIN tblCollections ON tblBusiness.BusinessID = tblCollections.BusinessID
    WHERE(tblCollections.LastCollectionDate<=Date()-7)
    GROUP BY tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency
    ORDER BY tblBusiness.BusinessName;

    Any help is much appreciated!

    Nick.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could use an IN operator perhaps?

    WHERE tblBusiness.BusinessID NOT IN (SELECT BusinessID FROM tblCollections WHERE LastCollectionDate <= dateadd("day", date(), -7))

    or something similar... the idea is to create another small dataset of business ID's that HAVE had a collection date in the last 7 days, then exclude matching id's from the main query.

    Alternately, you may want to try:

    WHERE Max(tblCollections.LastCollectionDate) <= dateadd("day", date(), -7)
    Last edited by Teddy; 06-03-04 at 14:22.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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