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
GROUP BY tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency
ORDER BY tblBusiness.BusinessName;