Macro to show combinations in an Access column?

[yomero]

Hi,

In MS Access 2010 I have a Table with multiple columns:

1. User
2. iPhone
3. IPad
4. Android_Tablet
5. Android_Smartphone

For columns 2 to 5, the values are only 0(zer0) and 1. If 1, it means the user has such device.
I need a 6th column that will tell me which devices a user has. For example if user Juan Smith uses (iPhone and iPad and Android_Tablet) the 6th column would say

1. Juan Smith
6. iPhone+iPad+Android_Tablet

With that format, then I can use columns 1 and 6 to make pivot tables and graphs.

I am able to do this in Excel using loops and arrays. Excel is not an option anymore, since I have a vast amount of data, Excel takes hours versus Access takes seconds.

Here is a sample of my VBA script in Excel.

'The firt loop travels vertically...top to bottomFor arrRow = 2 To UBound(arrCombos) '- 1
'this array travels horizontally, left to right
For arrColumn = 3 To 8
    If arrCombos(arrRow, arrColumn) = 1 Then
        If nmCombo = "" Then
            nmCombo = arrCombos(1, arrColumn)
        Else
            nmCombo = nmCombo & "+" & arrCombos(1, arrColumn)
        End If
        arrCombos(arrRow, 9) = nmCombo
    Else
    End If
Next
If nmCombo = "" Then arrCombos(arrRow, 9) = "NO users"
nmCombo = ""
Next

 

[yomero]

I think I found a similar approach, but not exaclty what i want. i already have unique list of users in my table...i need to concatenate the "column title" if the value in each column is 1. Since I have 6 columns to concatenate (6 different devices), that would be 126 combinations....hence nested iif would be hell..and can you nest 126 iifs anyway???


Concatenate Column Values from Multiple Rows into a Single Column with Access — DatabaseJournal.com

 

[Joe4]

First things first, all calculations of this type should be done on the Query level, not on the Table level. So we will want to create a Calculated Field in a Query that will return what you want. You could write a series of IF statements to build it, or you could create your own Function (a User Defined Function) to handle it. I prefer the latter option, as it is easier to follow/maintain.

Here is the UDF that I came up with:

Function Combos(iPhone As Variant, IPad As Variant, Android_Tablet As Variant, Android_Smartphone As Variant) As String

    Dim myString As String
    
    If iPhone = 1 Then myString = "iPhone+"
    If IPad = 1 Then myString = myString & "IPad+"
    If Android_Tablet = 1 Then myString = myString & "Android_Tablet+"
    If Android_Smartphone = 1 Then myString = myString & "Android_Smartphone+"
    
    If Len(myString & "x") > 1 Then
        Combos = Left(myString, Len(myString) - 1)
    Else
        Combos = ""
    End If

End Function

So, you would just place this in a VBA module in your Access database, then use it like any other function. There are four arguments, one for each of your flags. So in your query, your calculated field in your query would look something like this:

AllDevices: Combos([iPhone],[IPad],[Android_Tablet],[Android_Smartphone])

Note that all the arguments enclosed in brackets are the names of your table field names. So those need to match exactly in the calculated statement above.

This should return what you want.

 

[Joe4]

For a non-VBA approach, using strictly calculated query fields, I would use two formula to do this:
First:

Temp1: IIf([iPhone],"iPhone+","") & IIf([IPad],"IPad+","") & IIf([Android_Tablet],"Android_Tablet+","") & IIf([Android_Smartphone],"Android_Smartphone+","")

Then:

AllDevices: IIf([Temp1]="","",Left([Temp1],Len([Temp1])-1))

 

[yomero]

Works like a charm. Thank you Joe4!

 

[yomero]

Of course!!! this NON-VBA approach was on my mind, but never got to nail it down. Thanks again Joe4!