SELECT MAX DATE

[whicks]

Help SQL, trying to view only max [term date] rows,

SELECT [Rolling 12 - June report].EmplID, [Rolling 12 - June
report].Name, [Rolling 12 - June report].[Current Status], [Rolling 12
- June report].[Eff Date], [Rolling 12 - June report].[Term Date],
[Rolling 12 - June report].[Term Month], [Rolling 12 - June
report].Action, [Rolling 12 - June report].Reason, [Rolling 12 - June
report].[Reason Descr], [Rolling 12 - June report].[Vol/Invol],
[Rolling 12 - June report].[Hire Date], [Rolling 12 - June report].
[Rehire Dt], [Rolling 12 - June report].[Adj Service Dt], [Rolling 12
- June report].[Job Code], [Rolling 12 - June report].[Job Code
Descr], [Rolling 12 - June report].[Job Family], [Rolling 12 - June
report].[Product ID], [Rolling 12 - June report].[Product ID Descr],
[Rolling 12 - June report].Location, [Rolling 12 - June report].
[Location Descr], [Rolling 12 - June report].[Geo Area], [Rolling 12 -
June report].[Process Area], [Rolling 12 - June report].BU, [Rolling
12 - June report].[HR Segment], [Rolling 12 - June report].[HR Segment
Descr], [Rolling 12 - June report].[HR Segment 2], [Rolling 12 - June
report].[Clearance Doc], [Rolling 12 - June report].[Resignation
Letter], [Rolling 12 - June report].[Intern Clearance List]
FROM [Rolling 12 - June report]
WHERE (([Rolling 12 - June report].[Term Date])=(select max( [Term
Date] ) from [Rolling 12 - June report]));

Some duplicates appear in [Rolling 12 - June report] and I only want
to see the max term row. Group By Max does not work.

Any help?

 

[whicks]

Nevrmind, got it.

SELECT DISTINCT * INTO [Rolling 12 - June report2]
FROM [Rolling 12 - June report]
WHERE ((([Rolling 12 - June report].[Term Date])=(SELECT MAX( [Term
Date] )
FROM [Rolling 12 - June report] As tb_h1
WHERE tb_h1.EmplID = [Rolling 12 - June report].EmplID)));

There is a million ways to do the same operation, feel free to add
other examples.

 

[whicks]

The above was a MAKE TABLE, example w/o MAKE TABLE;

SELECT DISTINCT *
FROM [Rolling 12 - June report]
WHERE ((([Rolling 12 - June report].[Term Date])=(SELECT MAX( [Term
Date] )
FROM [Rolling 12 - June report] As tb_h1
WHERE tb_h1.EmplID = [Rolling 12 - June report].EmplID)));

BTW, please don't remind me about Keywords in Access like NAME & DATE,
I know.