Criteria Across Two Fields

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

A bit confusing, but this should get you started.

You'll need two tables with a one to many relationship. The first table is tblCases which contains 1 record per case. The second table is tblReports.

tblReports contain the summaries and have a one to many relationship with tblCases, as each case will have one or more reports. One of the fields in tblReports will contain the date of the report. Another will contain the summary.

You'll establish a relationship between the two tables with a field such as a CaseID.

You're trying to generate a report that lists each case and the latest summary that exists for it. To do this, you'll design a query that is based upon both tables. The goal of the query is to get it to return one record per case while selecting the latest record from tblReports based upon the report date.

The query is going to go something like this. The code is rough but it should give you an idea of in what direction to proceed.

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 (though in fact, the case names correspond to unique IDs in another table, and the dates correspond to unique IDs in a third 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

JayPoppin said:

Say its all in one table . . .

Is there a way to do this?

Click to expand...

It shouldn't be all in one table, as it violates the rules of normalization and may make it impossible for you to accomplish what you want. You'll save yourself a lot of aggravation regarding this issue, as well as others that will inevitably crop up, if you take the time to design your tables and relationships properly.

I'm not going to discuss DB design but answer your question as the one table you talk about might actually be a query result, so to get from

This

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

To this

Case Name | Case Summary | Date

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

Can be done simply with 2 queries I will use your field names tho I hope one isn't Date and I would also avoid spaces as they cause problems and also require []

Query1 is an aggregate query selecting case name and Date Groupby on the former and Max on the latter,
Query2 joins query1 to the table (or query that generated that data) on case name and maxdate to date and pulls all the data required.


Brian

Awesome. Thank you.