Identifying non-duplicate records

[kevinlcarlson]

I have a table containing a 'data' field and a 'status' field. I want to set the 'status' value to 1 for each record that has exactly one occurrence of 'data' in the table. I.e. if the value of 'data' occurs in multiple records, then do not update the 'status' field.

Is there some way to do this with SQL alone? I wrote some VBA code to step through the recordset of items having count(*) = 1 and executing a RunSQL within the loop to update 'status', but this runs extremely slowly...

Suggestions?

Thanks!
Kevin

[nico5038]

For a fast processing you would need a "query only" solution, but without an additional unique ID (e.g. an autonumber), you can only use a Dcount() function that will also slowdown processing :-(
With an autonumber you can create a groupby query counting the occurrences and determing the Minimum ID.
That query can be used to update the table with the Status = 1 when the record's ID Exists in that groupby query.

When only "single" records are needed, then limit the Groupby query to return only data for the Count(*) = 1.

Getting the idea ?

Nic;o)