Only retrieve last history item

[rossmcauslan]

I have two tables in my db called case and history. There is a one to many relationship between the caseid field in both tables. The case table holds details on each case within the database and the history table holds details on the history of the case. History contains the following fields: CaseID, NoteDate and NoteText. There often more that one history item to each CaseID.

The problem I have is that I need to create a report that will only show the caseid's where there has not been any activity in the last 7 days. How do I create a query that only shows the most recent history item for each case.

This doesn't seem to be too dificult but I can't seem to get my head round it!

Thanks in advance.

 

[PeeweeNo1]

If I have understood your query correctly this is similar to something I have done:

Create a query with the fields CaseID (from your case table) and NoteDate(from your history table). Then group by max NoteDate (by clicking on the Totals icon at the top)

This will find the latest date for each CaseID. This might be all that you need, however if you are wanting extra information in the query you will need to do the following bit:

Create another query with whatever data you want in it. Eg CaseID, NoteText, NoteDate and also include the above query in this query. Create a join between NoteDate and the MaxNoteDate. This should then only show the most recent history item.