Complex Query - maybe 2?

[davesmith202]

I have two tables:

1. ActivityLog: ID, Customer, Date, Activity, Details, ContactSuccess (

2. Contacts: ID, Company, ContactFirstName, ContactLastName

I want to create a Query that filters records on only the last Date in the ActivityLog table for each Customer where Date>45 days ago AND ContactSuccess=Yes

The idea is that you may have multiple Activity Log records for teh same customer, but we want to only find those that haven't been contacted in the last 45 days and where contactsuccess=Yes.

Then, I need to show the Company and Contact Name which meets this criteria, for each company. Essentially, you are left with a list of people you need to contact since you have not had successful contact with them in the last 45 days.

I'm thinking maybe this needs two Queries, with one a Totals Query but need some help!

Thanks,

Dave

 

[Brianwarnock]

Yes you will need 2 queries but you haven't said what part you are having difficulty with.
The first will group on ID max on date with a criteria of between Date() and Date()-45 and where with criteria ="yes" for contact success, then join this to other table.

Brian

 

[davesmith202]

Thanks Brian.

I think the problem I have is that I am not quite sure how I do the filter Criteria, because if it is a field I just want to filter by, what do I put in the Total row? Do I just leave it as Group by? If I do that, won't it group by just a filtering field?

 

[Brianwarnock]

As I said you select Where, the last option from the drop down, Access will autamtically remove the Show .

Brian

 

[davesmith202]

I never knew that before. That helps a lot, with this problem and many other queries! Thanks.

 

[Brianwarnock]

Happy to have helped, its brightened up a bad day. Made a fool of myself on another thread.

Brian

 

[davesmith202]

Oh dear! Don't worry, you helped me out big time.

 

[davesmith202]

By the way, there is something else related to these queries I would like to do. On my Switchboard, I would like it to display the record count for this queries output.

How would I do that?