Query needs to filter to max date

[Jodonn]

Hi. I've got fairly simple knowledge when it comes to Access so please go easy on me. I'm currently :banghead: trying to get this to work.

I have a form where the user enteres details about a customer's case. Within that form is a subform for the user to enter the date of every letter received by the customer and the date it was responded to. This is stored on a seperate table to all the other data (to allow for infinite dates to be entered), but both tables are linked using a reference number.

I also have a query that will eventually be used to produce a report for management. The query pulls everything of a certain criteria, but the issue is where there are multiple letters on a case, there are also multiple lines in the query for the same case each with every individual letter received. I need the query to return only the most recent letter received and it's acknowledgement date. If I use the MAX option, it only does the max for date received and doesn't return that entries equivalent responded date - it returns one of the other random responded date.

I hope this makes sense. The query SQL is:

SELECT [Complaint Summary].[Case Reference], [Complaint Summary].[Customer Name], [Complaint Summary].[Date In], [Complaint Summary].Month, [Complaint Summary].[New or Existing?], [Complaint Summary].Overview, [Complaint Summary].[Issue Type], [Complaint Summary].[Case Manager], [Complaint Summary].[Reason For Contact - CS], [Complaint Summary].[Model Number], [Complaint Summary].Age, [Complaint Summary].Mileage, [Complaint Summary].[Dealer Code], [Complaint Summary].[Latest Update], [Complaint Summary].Status, [Complaint Summary].[Over 3 Weeks], [Complaint Summary].[Goodwill Applied?], [Complaint Summary].[Last Contact], [Complaint Summary].[Next Contact], [Complaint Summary].[Resolved Date], [Complaint Summary].[Closed Date], Correspondence.[Correspondence Received], Correspondence.Acknowledged
FROM [Complaint Summary] LEFT JOIN Correspondence ON [Complaint Summary].[Case Reference] = Correspondence.[Reference Number]
WHERE ((([Complaint Summary].Status)<>"Closed") AND (([Complaint Summary].[Include On MD Log])="-1"));

 

[GinaWhipp]

Don't try to do this all in one query...

For your Report you'll probably want (need) two queries, like you did on the Form. The Subreport would contain the multiple lines for each letter and then in that second query you can get your MAX date.

 

[gblack]

Yes, if you want to only get data based on some MAX field, you first have to make the aggregated query and group by your ID field. I am not certain, from your post exactly what you need.

I am making some assumptions as to your [Complaint Summary].[Case Reference] field. If that value is different for each record, then the below code will not work. However, if that field is the same for:

multiple lines in the query for the same case...

Meaning if your case reference has the same number for each record of the same letter, then the code (below) might get you what you need... Give it a shot at least.

SELECT 
[Complaint Summary].[Case Reference], 
[Complaint Summary].[Customer Name], 
[Complaint Summary].[Date In], 
[Complaint Summary].Month, 
[Complaint Summary].[New or Existing?], 
[Complaint Summary].Overview, 
[Complaint Summary].[Issue Type], 
[Complaint Summary].[Case Manager], 
[Complaint Summary].[Reason For Contact - CS], 
[Complaint Summary].[Model Number], 
[Complaint Summary].Age, 
[Complaint Summary].Mileage, 
[Complaint Summary].[Dealer Code], 
[Complaint Summary].[Latest Update], 
[Complaint Summary].Status, 
[Complaint Summary].[Over 3 Weeks], 
[Complaint Summary].[Goodwill Applied?], 
[Complaint Summary].[Last Contact], 
[Complaint Summary].[Next Contact], 
[Complaint Summary].[Resolved Date], 
[Complaint Summary].[Closed Date], 
Correspondence.[Correspondence Received], 
Correspondence.Acknowledged
FROM [Complaint Summary] LEFT JOIN 
Correspondence ON [Complaint Summary].[Case Reference] = Correspondence.[Reference Number] INNER JOIN 
(SELECT 
Correspondence.[Reference Number], MAX(Correspondence.[Correspondence Received]) AS MOST_RECENT
GROUP BY Correspondence.[Reference Number]) AS MR ON
[Complaint Summary].[Case Reference] = MR.[Reference Number] AND Correspondence.[Correspondence Received] = MR.MOST_RECENT
WHERE ((([Complaint Summary].Status)<>"Closed") AND (([Complaint Summary].[Include On MD Log])="-1"));

 

[Jodonn]

GinaWhipp - thanks. I've tried using a 2nd query to find the max, but changing the Total option to Max changes nothing in the query. It still returns every entry even when multiple entries have the same reference number.

gblack - thanks. I'll try and explain it better. Each case has a unique reference number. When letters are added to the customer's case, they are added to a seperate table which also includes the unique reference number to help tie them all together. So if a customer sends in 5 letters, each row has their reference number, the date the letter was received and the date the letter was responded to. There would be 5 rows for that reference number. I want the query to return the most recent letter received for each reference number as well as the relevant date that letter was responded to.

I did try your code but it brought up a Syntax error (missing operator) in query expression.

 

[GinaWhipp]

How did you create the second query? You will need two seperate queries, one attached to the Main Report and the second attached to the subreport. The second one which would attached to the subreport is the one you should get the MAX date from. I would not try to do this in one query.