TOP 3 Notes per Contact Query

[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 query in an Access2000 for a Contact Management database that will be used for a report in Crystal Reports. I'd like to make it a single query if I can. 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] in descending order) for each Contact. Ideally I'd like to have 1 line for each Contact with the following fields:

CGID
CGName [Optional]
1st NoteDate (Newest Note) [Optional]
1st NoteID
1st Note {This is a MEMO field} [Optional]
2nd NoteDate (2nd Newest Note) [Optional]
2nd NoteID
2nd Note {This is a MEMO field} [Optional]
3rd NoteDate (3rd Newest Note) [Optional]
3rd NoteID
3rd Note {This is a MEMO field} [Optional]

There are other fields involved, but if I can get above I can build from there. The optional fields noted above would make life easier, but if not I should be able to link them manually. One line for each Contact would make life much easier, even if I can only get the notes 1 per line (like below) I think I could still do what I need.

CGID
CGName [Optional]
NoteDate (Newest Note) [Optional]
NoteID
Note {This is a MEMO field} [Optional]

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}

The Tables are linked by ContactGroup.CGID to Note.CGID in a One-To-Many relationship.

 

[lupins46]

A query can give you one record per Note.
If you want the nootes added to the same record you would need some additional vba code to do that.
I've not tested this query so I hope it turns out OK

SELECT Note.CGID, Contactgroup.CGName, Note.NoteID
FROM Contactgroup INNER JOIN Note ON Contactgroup.CgID = Note.CgID
WHERE (Note.NoteID) In (SElect top 5 t.noteid from [note] as t where [note].cgid = t.cgid order by t.notedate desc, t.noteid desc)
ORDER BY Note.CgID;