help printing notes from our database 2

[sbelongie]

Ok, here is the situation that I am looking at.
and I am having problems trying to figure out how to do this in my head for some reason
what I want to do is

I want to print out all of the accounts that we have in a region - I got this done
I want to print only the last note date by an account – right now I am getting all of the note dates
and I also only want to print the accounts that have not had a note in the past year or so
-I don’t want to show the accounts that have notes after a year


this way we can see what accounts need to be contacted

if you guys could help me out with this that would be great
thanks

Steve

 

[synapsevampire]

Logic doesn't work "for the past year or so", you must explicitly state requirements.

If I'm understanding the requirements correctly, you want those without any notes, or the most recent which is at LEAST 1 year old?

Group by the Account

In the Report->Edit Selection Formula->Group and place something like:

isnull({table.notedate})
or
(
{table.notedate}<=dateadd("yyyy",-1,currentdate)
and
{table.notedate}=maximum({table.notedate},{table.account})
)

Should work, I can't test right now.

-k

 

[sbelongie]

sorry about the confusion I am looking for accounts that have no actvitiy for notes older than a year

 

[lbass]

Is the date field specific to notes, or could the date field reflect activities other than notes? Also, your response is still not clear. Do you mean that you only want to show accounts that have no note activity within the last year?

-LB

 

[sbelongie]

The date field that I would be using is specific to the notes and yes I want to show the accounts that have no activity within the last year.
sorry I am confusing you guys

 

[lbass]

First group on {table.account} and then create a formula {@isnull}:

if isnull({table.notedate}) then 0 else 1

Then go to report->edit selection formula->GROUP and entering:

sum({@isnull},{table.account}) = 0 or
maximum({table.notedate},{table.account}) < dateadd("yyyy",-1,currentdate)

This will give you the accounts that have never had a note or whose most recent note is dated more than a year ago.

-LB

 

[synapsevampire]

Hmmm, are you certain that my method doesn't produce the same results, LB?

-k

 

[lbass]

SV,

It depends on the database structure. The second part of your formula is the equivalent to:

maximum({table.notedate},{table.account}) < dateadd("yyyy",-1,currentdate)

The first part of the formula is where we differ. If an account can have multiple records, with some having note dates and others being null, as in:

Acct NoteDate SomethingElse
1 9/30/04 A
1 B
1 9/19/04 C

Then your method would return Acct#1, since at least one record was null for note date, even though the dates did not meet the date criterion, while my method would not return Acct#1, since not all of the notedates were null.

It is possible that Note Date comes from a second table that only contains notes and if there was a one to many relationship between the first table and the notes table, then a null would always mean there were no notes, and your method would work equally well. My method would work regardless, I think.

-LB