Most Recent Date Criteria Across Two Fields

[jaypoppin]

Hello,



I need help with what seems to be a basic problem. I'm going to try to cut out all of the superfluous elements and explain it in the following way:

Say I have a query which is pulling data (in memo form) that records different language I have used to summarize different cases I am involved in, in different reports that have been produced over the past several years.

Some cases are summarized in several different reports, some cases are only summarized in one report. The main table in question assigns a unique ID number to each summary, recording what its a summary of (i.e. which case it summarizes, which it selects from another table), and which report it was in (which it selects from another table).

Each report is dated, and so each summary is dated by proxy.

It is easy for me to make a query that lists all summaries sorted first by the name of the case being summarized, and sorted second by the date of the report in which the summary appeared.

What I would like to be able to do is the following: for every unique case name, I want to return only the most recent summary. I.e. I don't want to return multiple summaries for case names for which there are multiple summaries stored in the underlying table, but only the most recent one.

I hope that wasn't too convoluted.

Thanks,

Jay

[June7]

Requires subquery or DMax.

Build a query that joins tables so all related information is available. Then try another query:

SELECT * FROM query1 WHERE ReportDate= DMax("ReportDate", "tablename", "CaseName='" & [CaseName] & "'");

[jaypoppin]

Thanks, though I'm unable to tell if this works because when it runs it prompts me for a date.

Let me try to re-explain.

Say its all in one table:

Case Name | Case Summary | Date

Case 1 | Lorem ipsum dolor... | DateX
Case 1 | Lorem ipsum dolor... | DateY
Case 2 | Lorem ipsum dolor... | DateX
Case 3 | Lorem ipsum dolor... | DateZ

And I want to return only the most recent Case Summary for each unique Case Name. So, assuming X is more recent than Y is more recent than Z, I want to return the following:

Case Name | Case Summary | Date

Case 1 | Lorem ipsum dolor... | DateX
Case 2 | Lorem ipsum dolor... | DateX
Case 3 | Lorem ipsum dolor... | DateZ

Is there a way to do this? (I might also be messing up what identifiers take [] and which don't in Access.)

Thanks so much,

Jay

[June7]

The [] are used with table and field names. They are required if name contains spaces or special characaters or are reserved word.

My example used ReportDate and CaseName as field names. Use your real field names, possibly:

SELECT * FROM [your table or query name here] WHERE [Date]= DMax("[Date]", "[your table or query name]", "[Case Name]='" & [Case Name] & "'");

[jaypoppin]

So I used that code, but when I run the query I'm prompted to enter a parameter value for Date?

The [] are used with table and field names. They are required if name contains spaces or special characaters or are reserved word.

My example used ReportDate and CaseName as field names. Use your real field names, possibly:

SELECT * FROM [your table or query name here] WHERE [Date]= DMax("[Date]", "[your table or query name]", "[Case Name]='" & [Case Name] & "'");

[jaypoppin]

For reference, the code is:

SELECT *
FROM [Case Summaries]
WHERE [Date]= DMax("[Date]", "[Case Summaries]", "[Case Name]='" & [Case Name] & "'");