Query Conundrum - Looking at Constituents by their Actions... help?

Tom Miller:
Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Can you be a little more specific on who you want to include & exclude in the query? Is it

1. Everyone who does not have action 1, 2, or 3 on their record this year, AND has action 1, 2, or 3 on their record from a previous
2. Everyone who does not have action 1, 2, or 3 on their record this year, regardless of whether they have the action from a previous year

Those two outputs will require differing criteria.

Tom Miller:
Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

Michael Sherman:
Hi Tom, I don't know why your query doesn't work. It works for me: Action Type one of 'Action 1', 'Action 2', 'Action 3' AND Action Date does not equal 'This Calendar Yr'---No parenthesis My Output is Auction Type, Auction Date, and Name. Mr. Bloggs shows up with Action 1 - 9/24/13 and Action 3 - 9/24/12. When I add Action 2 for 9/24/14 he still shows up but with only Actions 1 & 2.

Tom Miller:
[quote user="Josh Bekerman"]

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[/quote] Hi Josh, This has crossed my mind, however I require the query to be dynamic - and i do believe merging queries makes them static? This query forms the basis for a dashboard panel, and therefore must be dynamic. I will post more detailed overview. Thanks all for your replies. Tom.