Create New table with DISTINCT 1

[rccline]

I have a table with the following type of data

tblNAMES
ID Name
1 Bill
1 Bill
2 Nancy
3 Jane
3 Jane
3 Jane


I can run SELECT DISTINCT tblNames.ID
FROM tblNames;

Which provides me with the distinct ID's.

I want to create a table with DISTINCT ID and the associated Name for that ID:

tblNAMES2
ID Name
1 Bill
2 Nancy
3 Jane


I tried self join and a number of other options, but I can't get the results I need to create a table with unique ID and the associated name.

There must be a way.

Thanks.

Robert

 

[PHV]

SELECT ID, Max([Name]) INTO newTable
FROM tblNAMES GROUP BY ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rccline]

This query created newTable with Distinct ID's but no names were imported into the newtable

 

[rccline]

Sorry PHV, I had misspelled the name field. THis query works GREAT! THANK YOU!!!

Robert