TOP 3 Notes per Contact Report

[DevilsSlide]

Can someone HELP !!! or make any suggestions PLZ !!!

I know this is long winded, but I wanted to try to make sure I provided all info I thought might be needed - I hope I covered it all.

I am trying to get help to create a CR9 report for an Access2000 for a Contact Management database. What I'd like to get is the last 3 notes (based on Date & NoteID [NoteID is optional, but would be helpful because several Notes may be entered for each Contact daily to get truly most current] in descending order) for each Contact. Ideally I'd like to have following fields:

CGName (Grouped by)
1st NoteDate (Newest Note)
1st Note {This is a MEMO field}
2nd NoteDate (2nd Newest Note)
2nd Note {This is a MEMO field}
3rd NoteDate (3rd Newest Note)
3rd Note {This is a MEMO field}

There are other fields involved, but if I can get above I can build from there.

DB Tables involved: ContactGroup & Note

Fields:
ContactGroup.CGID (AutoNumber - Primary Key)
ContactGroup.CGName

Note.NoteID (AutoNumber - Primary Key)
Note.CGID
Note.NoteDate
Note.Note {This is a MEMO field}

Any Help would be Greatly Appreciated
John

 

[synapsevampire]

Sounds doable.

The grouping seems fine, add the notedate to the details, right click it and select insert->Summary->Maximum.

Now select Report->Top N->and select the For this Group sort TOP N, and then in the based on select the max of date field, and select where N is 3.

Uncheck the include Others.

Should do it.

-k

 

[DevilsSlide]

-k,

I think you are pointing me in the right general direction, but I must be a bonehead. When I go to apply the TOP N it only has the option for ContactGroup.CGName and when I apply the TOP 3 it results in listing only the Contacts who have the 3 most resent Notes. Not 3 Notes for each Contact, but 3 Contacts with all of their Notes.

Any thoughts on where I’m FUBARing?

I think I’m only that far from being close!

Thanks
John

 

[lbass]

Add {Note.NoteDate} and {Note.Note} to your details section and sort by {Note.NoteDate} in descending order. Then create a running total {#cntwingrp} using the running total expert: Note.NoteID, count, evaluate for each record, reset on change of Group (Contact). Then go to format->section->details->suppress->x+2 and enter:

{#cntwingrp} > 3

-LB