How to pull first instance of ID into a table

[tortelvis]

I have a table with duplicates, and I need help writing a query or sql statement that will pull the first occurance of an ID field and all the other fields with it into a new table.

Anybody?

Thanks

-Tort

 

[perlgurl]

If I understand you correctly you just need to use distinct:

SELECT DISTINCT tblName.ID, tblName.field1, tblName.field2, tblName.field3 FROM tblName

Joyce

 

[JeremyNYC]

What do you mean by "first occurance"? Lowest number? First one entered? First one when sorted by a particular field?

Records are not stored i any particular order in a table ina database, so it's important to know what you're looking for.

Jeremy

PS: Long Live Dread Zep =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[tortelvis]

Thanks, The Select Distinct did it - i was looking for a way to grab unique occurances of several fields.

-Tort

 

[rccline]

This didn't work for me. The record itself is not distinct, only the first field.

tblNames:

RowID NameID FirstName LastName
1 300 Jane Smith
2 300 Jane Smith
3 300 Jane Smith
4 400 Joe Smith
5 400 Joe Smith
6 400 Jim Smith


I only want to list the first instance of NameID with associated fields within the record:

RowID NameID FirstName LastName
1 300 Jane Smith
4 400 Joe Smith

SELECT DISTINCT tblName.NameID, tblName.FirstName From tblName

this sql brings up all of the records. Select Distinct for one field is not enough info.

Thanks.

Robert

 

[mrf1xa]

Robert

Had a play around and this seems to produce what you want in small test db using a table set up like yours:

SELECT Table1.NameID, First(Table1.FirstName) AS FirstOfFirstName, First(Table1.Surname) AS FirstOfSurname, First(Table1.RowID) AS FirstOfRowID
FROM Table1
GROUP BY Table1.NameID;

HTH Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....