One business, many collections, list week old collections

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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi Nick

Using your existing query, enter the following into the critieria of the field named "LastCollectionDate" :

<=Now()-7

This will exclude businesses who had a collection within the last week - you might want to try < rather than <= to get the results you want from the query.

To restrict the businesses to those on a weekly "frequency", add the "frequency" field to your query and use the criteria "=Weekly" or "=W" (depending on how you stored the frequency value). Be sure to use your actual variable name (I assumed "frequiency") and remove the quotes from the criteria.

HTH, Andrew. :)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top