Query to filter for Customers

[davesmith202]

I have an Activity Log table with the following fields:

ActivityID
Customer
Date
Activity Type
Details
Contact Success

I want to filter for Customers where there has never been any "Post Out" entries in the Activity Type field. I can't just do a filter for Post Out because it would then show multiple entries for the same Customer.

What Query would achieve this? Some type of Totals Query or something?

Thanks,

Dave

 

[dcb]

Access Version?

 

[dcb]

Sorry dont worry I was thinking about using "IN"

How about

SELECT DISTINCT ActivityLog.Customer
FROM ActivityLog
WHERE ((Not (ActivityLog.ActivityType)="Post Out"));

 

[davesmith202]

I am not sure that is quite what I am looking for. Consider the following data:

Customer, Date, Activity Type

1, 1/1/10, Mailshot
2, 3/1/10, Post Out
4, 4/1/10, Mailshot
4, 6/1/10, Mailshot
5, 7/1/10, Post Out
1, 8/1/10, Post Out

The filtered list should not show Customer 1 because they have had a Post Out on 8/1/10. The only record that matches would be Customer 4, since never in their history is there a Post Out entry.

How could this be done?

 

[DCrake]

I think you need to record whether the company has had a post out at company level, not activity level. So when you choose post out as an activity it updates a flag on the parent record to denote the company has had a post out. Then if you run a query based on this flag you will get all the ones without a post out.

David

 

[davesmith202]

I agree that would be a good solution, but I don't have that option at present.

What alternative method could I use to filter the data?

I am thinking that maybe there needs to be some type of grouping going on using a totals query. Or perhaps bringing in another table and use a join to get only the customers that don't have any history with Post Out in it.

Any ideas?

 

[dcb]

First query will only work in 2007 or later as it uses IN
Second one is based on summing the post out entry's - Convert post out to 1 & 0, Sum them and select only 0

SELECT DISTINCT ActivityLog.Customer
FROM ActivityLog
WHERE (((ActivityLog.Customer) Not In (SELECT DISTINCT ActivityLog.Customer
FROM ActivityLog
WHERE (((ActivityLog.ActivityType)="Post Out"));
)));

SELECT DISTINCT ActivityLog.Customer
FROM ActivityLog
GROUP BY ActivityLog.Customer
HAVING (((Sum(IIf([ActivityType]="Post Out",1,0)))=0));

PS, Sorry didnt think through my first post (hope i did better this time)

 

[davesmith202]

What a fantastic solution! Very clever. Thank you! I couldn't work out how to do it. I love it when someone does an innovative solution like this. It is almost art!

 

[dcb]

What a fantastic solution! Very clever. Thank you! I couldn't work out how to do it. I love it when someone does an innovative solution like this. It is almost art!

Which one if i may ask?