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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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. :)
 

mrnicknick

New Member
Joined
Jul 8, 2004
Messages
23
Thanks!

Thanks Andrew, thats done it. I'll have to look up the 'having' clause some more.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,292
Messages
5,623,813
Members
415,991
Latest member
RicardoSS

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
Top