Specific Distinct Results in Search
I'm searching the Summary and Description fields in IncidentNotes then returning any matches, which works fine. However, if more than one incident note within the incident has the search term, then it displays multiple results for a single incident (currently). I would like it to display the incident along with the 'newest incident note' (highest IncidentNoteID) associated with that incident if the term is found (even if the newest incident note doesn't contain the search term).
ON Incidents.Incident = IncidentNotes.Incident
WHERE IncidentNotes.Description Like '*'&search&'*'
OR IncidentNotes.Summary Like '*'&search&'*'
I tried using DISTINCT to no avail and I'm also unsure of how to display the newest incident note for each incident returned. I'm very surprised I even made it this far but I seem to be picking stuff up a lot faster now (Rudy's site is great http://r937.com/).
Thanks for any help.