Restricting Query Data

[nvacek]

I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru
12/31/08 to take out duplicate accounts, this works great when I run the
report quarterly or yearly but I need to run this report monthly. I have
3 tables that work together and these dates are coming from my Activities
table, I keep track of when I have had contact with them.

I am looking to only count these people 1 time in a year (filter by start
date does not work is it is driven by actibvity date) for example if I
work with them in 1/08 run my report in 1/08 they should show up. If I work
with them in 2/08 I do not want them in my 2/08 report as they were
counted in
1/08.

How can I filter these clients out. What expression, operator word(s) or
do I need to do it in the SQL can I use. I don not want the ones I counted
again if i had contact with them in 2/08.

I have tried Select DISTINCT [Actibity Date] between 1/1/08 and 12/31/08

What string do I need to use to filter out the people I counted in 1/08
and so on. Is there a what if statement I can put in for example if i
had contact with them in 1/08 and again in 2/08 exclude them.

 

[Amy Blankenship]

I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru
12/31/08 to take out duplicate accounts, this works great when I run the
report quarterly or yearly but I need to run this report monthly. I have
3 tables that work together and these dates are coming from my Activities
table, I keep track of when I have had contact with them.

I am looking to only count these people 1 time in a year (filter by start
date does not work is it is driven by actibvity date) for example if I
work with them in 1/08 run my report in 1/08 they should show up. If I
work
with them in 2/08 I do not want them in my 2/08 report as they were
counted in
1/08.

How can I filter these clients out. What expression, operator word(s) or
do I need to do it in the SQL can I use. I don not want the ones I
counted
again if i had contact with them in 2/08.

I have tried Select DISTINCT [Actibity Date] between 1/1/08 and 12/31/08

Try pressing the "Totals" button and then you'll be able to select First
Activity date or Min Activity date. Once you have that query, store it. If
you run your monthly query against that, you should be able to only get the
ones that were first contacted between that month and the end of the year..

HTH;

Amy