Hi everyone,

I would really appreciate some help with this as its driving up the wall and i'm sure the answer isn't rocket science.

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.

so that what is returned is a list of all the companies that

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 npt 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.