0

I have a situation where records imported into an access database contain some duplicates values in a specific field. I do not want to remove any rows in the table with duplicates, rather just the duplicate values in the specific field in the relevant rows. I tried a building a query around a kind of split in string but is hasn't worked. How do I process this?

Example of rows in table currently

enter image description here

What I need to achieve

enter image description here

AJF
  • 1,801
  • 4
  • 27
  • 54
  • 2
    You will need to write a VBA function for this. MS Access is not sufficiently powerful. – Gordon Linoff Apr 07 '21 at 11:05
  • 2
    I do NOT see duplicates! – Maciej Los Apr 07 '21 at 11:09
  • As Gordon wrote, you need VBA. Per row: Split(), Dedupe, Join(), write back. I like https://stackoverflow.com/a/56594672/3820271 for Dedupe. – Andre Apr 07 '21 at 11:36
  • @GordonLinoff Thanks for response. yes, I have created an EXCEL VBA procedure to stop this occurring again before the import. Just need to deal with ones that have slipped past before now. What about using SQL Server? is it possible to do in SQL server? – AJF Apr 07 '21 at 11:44
  • @MaciejLos Rows 3, 6 and 8 have duplicate values within the field – AJF Apr 07 '21 at 11:50

2 Answers2

0

Take a deep look at your data...

If there's a duplicate within a field, every occurance of string is separated by , ,.

I'd suggest to create custom function as is described here

Public Function String_Split(sInput As String, _
                      lIndex As Long, _
                      Optional sDelim As String = ", ,") As String
    On Error GoTo Error_Handler
 
    String_Split = Split(sInput, sDelim)(lIndex)
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    String_Split = "Error!"
    Resume Error_Handler_Exit
End Function

Then, use it in update query:

UPDATE YourTable
SET [Needs Details] = String_Split([Needs Details], 0)

If you want to preview the result of String_Split function, before you run update query, use this:

SELECT [Pupil Ref], [Needs Details], String_Split([Needs Details], 0) NewDetails
FROM  YourTable

That's all!

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
0

You have duplication based on ", ," so if you find where that happens then take the left from that point.

Base your query on this:

select *,  iif(instr(field1,", ,")>1,  left(field1,instr(field1,", ,")-1), field1)
from table1
KeithL
  • 5,348
  • 3
  • 19
  • 25
  • I was going to do so much more for you but I got tired of working with Access. But basically... create a new table with the same columns and use the above query to insert records into it. – KeithL Apr 07 '21 at 14:09