Community
Showing results for 
Search instead for 
Do you mean 
Reply

Exclusion querys

Accepted Solution Solved
Copper Contributor
Posts: 7
Country: USA
Accepted Solution

Exclusion querys

I've created a bunch of queries that search on various name fileds in  the contacts table.

 

Eg: first name contain data and

       last name contain data and

       last name 1 contain data and

       contact  contain data

 

and every permutation of this where the different permutation are just about changing "contains data" to "does not contain data". This has left me with 12 queries - the total number of permutations.

 

Now the problem is when I add all the results of  these queries I get a number LESS than the total number of contacts!

 

Shouldn't all 12 of my permuations cover ALL contacts?

 

Can I run a query that excludes all these permutations to see what those records that don;t come up, are?

What would this look like?

 

Scratching my head over this one!

 

(To generate the permutations I used a matrix of binary numbers in Excel to track things and recorded the resulting totals

First Name  Last     Name Last Name 1 Contact
1 1 1 1
1 1 1 0
1 1 0 1
1 1 0 0
1 0 1 1
1 0 1 0
1 0 0 1
1 0 0 0
0 1 1 1
0 0 1 1
0 0 0 1
0 0 0 0

 

)

 

 


Accepted Solutions
Solution
Accepted by topic author AlexMartinez
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 7
Country: USA

Re: Exclusion querys

Hiya...

 

Thanks .... I actually messed up earlier... missing 4 of the possible queries in a 4-variable matrix.

(4 variables gives 2^4 = 16 queries) .. should have done this maths earlier :/

 

One of the missing queries gave me the missing sum and it all adds up now. Running the opposite queries got me to spot the missing ones!

 

Problem solved!

 

 

View solution in original post


All Replies
Silver Contributor
Posts: 1,813
Country: USA

Re: Exclusion querys

What happens when you take one of the queries and then run it's opposite?  do the numbers total up here

FN contants data and Last name contains data and Contact1 Contant data  

 

against

 

FN DOES NOT contain data and Last name FN DOES NOT contain data and Last name DOES NOT contain data

 

Does this work?

 

It sounds like you're not sure if your query is not right or if ACT is not generating appropriate results.  Start with a very simple query and build it from there.  (You can save the queries and then start with that saved query to save time)

Test Drive Act! and Quickbooks over Citrix http://GoTryAct.com

Jon Klubnik| ActTrainer.com
(866)710 4228

ACT! Certified Consultant / ACT! Premier Trainer / ACT! Hosting Provider

Solution
Accepted by topic author AlexMartinez
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 7
Country: USA

Re: Exclusion querys

Hiya...

 

Thanks .... I actually messed up earlier... missing 4 of the possible queries in a 4-variable matrix.

(4 variables gives 2^4 = 16 queries) .. should have done this maths earlier :/

 

One of the missing queries gave me the missing sum and it all adds up now. Running the opposite queries got me to spot the missing ones!

 

Problem solved!