Add, create a group id for simular records in a table.

[aquabp]

I am creating a project tracking database to track project files. The database pulls in file names into an access table. The table has FName and Path fields which are short text and a CAT field that is numeric. I have a query that ID's records that are duplicates. To date all I can do is Identify them as such. I want to provide a numeric value to each set of duplicate files. The query is below.

CurrentDb.Execute "UPDATE 02_Detail SET [02_Detail].FID = 1 WHERE ((([02_Detail].FName) In (SELECT [FName] FROM [02_Detail] As Tmp GROUP BY [FName] HAVING Count(*)>1 )));"

The final result would look like:

FName      Path      CAT
Dupfile1   path1      1
DupFile1   path2      1
DupFile2   Path1      2
DupFile2   Path1      2
DupFile2   Path2      2

I could then look at the duplicate file per category in a form or report to determine which file to keep. There are 1200 records with a total of 6,000 duplicate records that need to be reviewed otherwise I would just look at these files one by one.

Thanks in advance.

[ranman256]

If you key the fields in the table, you won't get duplicates.

[aquabp]

True, but this exercise cannot use that ability. I have pulled in the file names of actual project files from several work directories and put them into a table. I need to verify which is the master copy and determine what to do with other related files. So, in this case keying the table would leave thousands of files undetected out in the directory. I need to clean those files up. It would be a lot easier to group them in a form and look at the files in a sub form via the hyperlink that is on the sub form.

Hope this explains better what I am looking for.