Indentify Distinct records issue

[Programmer99]

I have a table that contains many SSNs and some of them are in the table more than once. What I'm trying to do is create a new field and populate the field to a value of "1" for each unique SSN.

For instance for the rows that have multiple SSNs, I want to value the first new field to 1 and the remaining same SSNs fields to 0. Any ideas of how to approach this?

Thanks.

 

[Golom]

Relational databases do not have any implicit ordering of records so concepts like "first", "last", "next" and "previous" are not really defined. Records are of course displayed in some order even if you don't use an ORDER BY clause but that ordering is not guaranteed. Further, even if you do ORDER BY SSN, where SSN is not unique (as in your case) there is no assurance about which record will be "first" for a given duplicate SSN.

You could do it with a recordset and some VB code but a strictly SQL solution is tricky at best and is meaningful only for a specific ordering of the records.

 

[Programmer99]

do you know the code I would use to connect to the table to create a recordset. I have experience in VB but am not sure how to link to an Access table in the module.

Thanks.