SELECT DISTINCT text field results in duplicates

[PRMiller2]

I am having a problem with SELECT DISTINCT... it is returning duplicate rows.

I have imported an Excel spreadsheet to a table with the following structure (just the first few columns):

HMSPIID, Text, 255, @
FIRSTNM, Text, 255, @
MIDDLENM, Text, 255, @
LASTNM, Text, 255, @

The first few rows of data look like this:

PI00GXV0G0 ALAN SCOTT JOHNSON
PI00GXV0G0 ALAN SCOTT JOHNSON
PI00GXV0G0 ALAN SCOTT JOHNSON
PI00K4WBF3 JANE MARIE SMITH
PI09TH3EG1 KATHY ANN JOHNSON

My query is as follows:

SELECT DISTINCT Trim(raw.HMSPIID) AS HMSKey, raw.FIRSTNM, raw.MIDDLENM, raw.LASTNM
FROM ImportHMSRaw AS raw
WHERE raw.HMSPIID Is Not Null;

The query returns the following:

HMSKEY FIRSTNM MIDDLENM LASTNM
PI00GXV0G0 ALAN SCOTT JOHNSON
PI00GXV0G0 ALAN SCOTT JOHNSON
PI00GXV0G0 ALAN SCOTT JOHNSON
PI00K4WBF3 JANE MARIE SMITH
PI09TH3EG1 KATHY ANN JOHNSON

I am using the Trim function as I had assumed there were extra spaces I didn't see. However, I'm still receiving duplicates. I always thought SELECT DISTINCT was pretty straightforward. Is it an issue with my table structure?

Thanks,
Paul

 

[PHV]

How about this ?

SELECT DISTINCT Trim(HMSPIID) AS HMSKey, Trim(FIRSTNM) AS HMS1ST, Trim(MIDDLENM) AS HMSMid, Trim(LASTNM) AS HMSLast
FROM ImportHMSRaw
WHERE HMSPIID Is Not Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PRMiller2]

Hey PHV,

I ended up shelving this for a bit -- and deleting the queries -- but I suspect I'll be looking this up again in the near future. Thanks for the reply!