Report with complex criteria

[davesmith202]

I have a table full of advertising results that looks something like this:

Keyword,Advert,Clicks,Clickthrough Rate
dog,abc,35,2.5%
dog,xyz,40,4.5%
cat,qwe,3,3.4%
cat,rew,22,1.5%
cat,ghj,34,1.7%
fish,hgf,56,4.5%
cow,tyr,22,3.3%
etc.

This data contains the results of trying different adverts to see which are the most effective in getting people to click on there adverts. By analysing this data, I can find the "winners" and so eleminate the poor performers.

So, I want to create a report that lists the keywords that meet the following criteria:

1. All keywords that are the same have over 30 clicks on each seperate advert. In the example above, only the dog keywords would be listed and not the cat keywords, since there are 2 cat ads that are under 30 clicks.

2. All keywords where there is only one advert type are not shown. e.g. fish in the example above.

How would I go about this?

Thanks,

Jon

 

[namliam]

1)
Turn it around, first select everything you do not want to see. Save this query.
Now do an outer join with this query to fetch everything that you do want to see.

2)
Again, subquery... Make a query to make a count of all that are one, save the query and join it in your final product.

Good luck!

 

[Brianwarnock]

You need to create 2 lists

1 Group and count on keywords where Count>1 The records must be in this list

2 Take the output from 1 and match against the list select distinct keyword and clicks <30 they must not be in this list

so the final join is keyword = keyword in 1 but where keyword Is Null from 2.

That's what I would try.


Brian

 

[davesmith202]

Thanks, I will play around with that.