SQL Need for Duplicates

[whicks]

I have a table of employee information.

EMPL_ID (Long Int)
NAME (Text)
HIRE DATE (Date/Time)
REHIRE DATE (Date/Time)
TERM DATE (Date/Time)
JOB TITLE (Text)
LOCATION CODE (Long Int)
etc, etc


I'm trying to delete duplicate records but keep the MAX term date.
Interns come and go multiple times. I use the Group By MAX on the
TERM DATE field in design view but it does not remove the duplicate
fields????

Anyways, does anyone have an example of some SQL to use here?

 

[Jeff Boyce]

First, Access treats some words as "reserved", and may not use the same
definition that you expect. One of those reserved words is "NAME" --
consider coming up with a different title for that field.

Second, if you would ever have need to sort by last name, or find all the
"Mary"s, it would help to use LastName and FirstName fields instead of one
big one ... and elementary database design argues for "one fact - one
field". Your "Name" field may contain more than one fact.

Do you really want to delete older (?historical) records, or do you wish to
only look at the most recent. "Deleting" is so final!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[whicks]

I wasn't aware of the reserved "key words" in Access, thanks.

This is not a database rather an extract from PeopleSoft. I am honing
down the information in Access and moving into Excel to post a roster
so other HR personnel can follow up.

Previously I just manually identified the Dupes and removed them.
But, I have queries that removes other standard 'exemptions' and
manual removal seem so time consuming because of number dupes keep
growing as fiscal year progresses.

This is my progression so far but as you can see I have not hit the
mark;

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]
FROM [Rolling 12 - June report]
WHERE [Rolling 12 - June report].[Term Date] = (SELECT Max[Term Date]
As [Term Date]);