Access table issue distinct

[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.

 

[ThomasLafferty]

This will return records for which the SSN is unique.

In SQL view of a new query, paste the following:

SELECT [COLOR=red]YourTable.SSN[/color], 
FROM [COLOR=red]YourTable[/color]
WHERE ((([COLOR=red]YourTable.SSN[/color]) In (SELECT [COLOR=red][SSN][/color] FROM [COLOR=red][YourTable][/color] As Tmp GROUP BY [COLOR=red][SSN][/color] HAVING Count(*)=1 )))
ORDER BY [COLOR=red]YourTable.SSN[/color];

You could then run an update query (after creating a field in your table to hold the 1s and 0s) and use the SSNs being pulled by your select as the condition to write 1s to your field.

You could then change this line in your SELECT query to greater than 1:

HAVING Count(*)[COLOR=red][b]>[/b][/color]1

Then run your update query using this condition (by refering to your SELECT query) to write in the 0s for the duplicated SSNs.

In effect then, you have 2 queries, one to select records, and one to update them. Not sure this is the best approach since there is undoubtedly a way to select and update in the same query, but my SQL is a bit weak. Incidentally, change the items in red to correspond to your table and field names.
Hope this makes sense...

Tom

Live once die twice; live twice die once.