Query to create a string from 12 fields (1 Viewer)

fat controller · Sep 26, 2017

Hi all - been a while since I have been working with Access, so bear with me as I have gotten rusty remarkably quickly.

I am looking to have a query that takes the data from 12 fields in a record, and then returns a string showing only each value once. These 12 fields contain only a few letters - for example, AB, DF, CF, STT and so on; they can also be null.

So, if I have the following: DF, AB, DF, DF, CF, I would like this to be shown on a report as DF, AB, CF. Is this possible?

Minty · Sep 26, 2017

What is the table structure? When the data is stored horizontally like this it quickly becomes difficult to aggregate it.

You almost want the heading from a cross tab query - but it sounds possibly as if your data isn't stored correctly.

fat controller · Sep 26, 2017

Hi Minty - sadly, it was one of those cases where there was no other option; essentially there are 14 (sorry, I thought it was 12) lines of fields that make up internal 'jobs', where the same job can be allocated across a number of different sites, selected by their code.

Changing the structure at this point is not an option I'm afraid.

What about doing this in a text box on a report (as essentially that is what it is for?) - - could I have the 14 fields (hidden) on the report and then a text box which shows the aggregated list of site codes? Nz function?

Minty · Sep 26, 2017

Firstly - I suspect you could normalise this data. You sound like you have ended up storing a matrix. That can definitley be normalised.

To solve your problem I think you are going to have to write a function, to loop around and compare each new result with the next one it finds - in pseudo code;

Code:

Initial Where Clause = ('')
For Each FieldToCheck
     If there is there a value Not IN Your Initial WhereClause
     Add it to the where clause
Next Field.

fat controller · Sep 26, 2017

Thanks - I will try and get onto it in the next couple of days

arnelgp · Sep 26, 2017

create a function in a module:

Code:

Public Function fnNoDup(ParamArray p() As Variant) As String

    Dim var As Variant
    For Each var In p
        If Trim(var & "") <> "" Then
            If InStr(fnNoDup, var) = 0 Then fnNoDup = fnNoDup & ", " & var
        End If
    Next
    If Len(fnNoDup) <> 0 Then fnNoDup = Mid(fnNoDup, 3)

End Function

then call it on your Query:

select fnNoDup(f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12)

where f1 to f12 are your fieldnames.

fat controller · Sep 28, 2017

Thank you both

@arnelgp - I went with your solution, and it works beautifully, not least as I can now also have an unbound textbox on the form that is populated on open, and shows a summary of the information. I couldn't have asked for better!

Query to create a string from 12 fields (1 Viewer)

fat controller

Slightly round the bend..

Minty

AWF VIP

fat controller

Slightly round the bend..

Minty

AWF VIP

fat controller

Slightly round the bend..

arnelgp

..forever waiting... waiting for jellybean!

fat controller

Slightly round the bend..

Similar threads

Users who are viewing this thread