CODEWITHSUNDEEP
ms-accessvbams-access-2013
IcyPopTarts
IcyPopTarts

Reputation: 504

Return Unique Records for Field

We have an access database create a csv list in an access table for each userid that logs into a computer. The problem is that if a userid logs in multiple times on one computer, than the userid is duplicated.

SO let's say this is what the table structure looks like

computer     DailyUsers
ABC-123      ml12, rs12, ml12, ml12, ee13
DEF-456      zx44, aa33, zx44

And this is what I want a query to return

Computer     DailyUsers
ABC-123      ml12, rs12, ee13
DEF-456      zx44, aa33

I tried using both Group By and Distinct but that looks on a row by row basis, not a field basis

How can this be achieved in Access 2013?

Upvotes: 0

Views: 42

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You can create a custom function in VBA to return only the unique users.

Add this in a VBA module:

Option Compare Database
Option Explicit

Public Function DistinctOnly(txt As String)

    Dim arr, v, rv As String
    Dim d As Object

    arr = Split(txt, ",")
    If UBound(arr) > 0 Then
        Set d = CreateObject("scripting.dictionary")
        For Each v In arr
            d(Trim(v)) = 1
        Next v
        rv = Join(d.keys, ",")
    Else
        rv = txt
    End If

    DistinctOnly = rv
End Function

Usage:

SELECT Table1.[ID], Table1.[Users], DistinctOnly(Table1.[Users]) as UsersX
FROM Table1;

Input data and query results:

enter image description here

Upvotes: 2

Related Questions