mrnicknick
New Member
- Joined
- Jul 8, 2004
- Messages
- 23
Hi,
I have two tables, one business, one collections. Each business has many collections.
I have written the following query to obtain the date of the last collection for each business.
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;
I am trying to write a query to return the businesses (along with last collection date) for those whose last collection date is a week or more older than today’s date. I know i will use the frequency field of the 'business' table to determine which businesses have a weekly collection but do not know how to write the actual query.
In a sort of psuedo code what i am trying to do is:
1. Get the last collection date for all businesses (done that in query above)
2. From those returned last collection dates, give me the last collection date and business name and address for each business whose 'frequency' field equals 'weekly' and whose last collection date is at least a week or more older than today’s date.
Any help would be appreciated. I am fairly new to SQL.
Nick.
I have two tables, one business, one collections. Each business has many collections.
I have written the following query to obtain the date of the last collection for each business.
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;
I am trying to write a query to return the businesses (along with last collection date) for those whose last collection date is a week or more older than today’s date. I know i will use the frequency field of the 'business' table to determine which businesses have a weekly collection but do not know how to write the actual query.
In a sort of psuedo code what i am trying to do is:
1. Get the last collection date for all businesses (done that in query above)
2. From those returned last collection dates, give me the last collection date and business name and address for each business whose 'frequency' field equals 'weekly' and whose last collection date is at least a week or more older than today’s date.
Any help would be appreciated. I am fairly new to SQL.
Nick.