Returning more than max

[Banaticus]

People
-----
Name
Number
Type

Notes (1 to many relationship with People table, based off Number)
-----
Number (lookup to People table)
Note
Date

I need to take all people of a certain type whose newest note is more than 6 months old. Here's what I have so far (it doesn't take into account the "more than 6 months old" part). Notice that I have the GROUP BY because of the MAX() in the outside SELECT statement:

SELECT [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[Soc Sec #], [Note Info].Note, Max([Note Info].[Eff Date]), [App Info].[App Type]
FROM [App Info] INNER JOIN [Note Info] ON [App Info].[Soc Sec #] = [Note Info].[Soc Sec]
WHERE [App Info].[App Type] Like 'Apprentice*' AND [Note Info].[Eff Date] in (SELECT [Note Info].[Eff Date]
FROM [Note Info]
WHERE  [Note Info].[Eff Date] Is Not Null)
GROUP BY [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[Soc Sec #], [Note Info].Note, [App Info].[App Type];

Problem is, it's returning multiple notes for a given person.

 

[Jon K]

Try these two queries:

qryOne:-
SELECT [Soc Sec], Max([Eff Date]) AS [MaxOfEff Date]
FROM [Note Info]
WHERE [Eff Date]<DateAdd("m",-6,Date())
GROUP BY [Soc Sec];

qryTwo:-
SELECT [App Info].[Soc Sec #], [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[App Type], [Note Info].Note, [Note Info].[Eff Date]
FROM [App Info] INNER JOIN ([Note Info] INNER JOIN qryOne ON ([Note Info].[Eff Date] = qryOne.[MaxOfEff Date]) AND ([Note Info].[Soc Sec] = qryOne.[Soc Sec])) ON [App Info].[Soc Sec #] = [Note Info].[Soc Sec]
WHERE [App Info].[App Type] Like 'Apprentice*';


Run qryTwo.
.

 

[Banaticus]

Thanks, I appreciate the help.

 

[Banaticus]

Ah, got it -- it was returning the maximum date that was more than 6 months ago. So, if a person had a note of
8/1/2005
1/3/2006
It was returning 8/1/2005. So I moved the test for Eff Date to the second query.

SELECT [Soc Sec], Max([Eff Date]) AS [MaxOfEff Date]
FROM [Note Info]
GROUP BY [Soc Sec];

SELECT [App Info].[Soc Sec #], [App Info].[Last Name], [App Info].[First Name], [App Info].MA, [App Info].Suffix, [App Info].[App Type], [Note Info].Note, [Note Info].[Eff Date]
FROM [App Info] INNER JOIN ([Note Info] INNER JOIN aaaatest1 ON ([Note Info].[Soc Sec]=aaaatest1.[Soc Sec]) AND ([Note Info].[Eff Date]=aaaatest1.[MaxOfEff Date])) ON [App Info].[Soc Sec #]=[Note Info].[Soc Sec]
WHERE ((([App Info].[App Type]) Like 'Apprentice*') AND (([Note Info].Note) Like 'Apprentice*')) AND [Eff Date]<DateAdd("m",-6,Date());