Combining unique field values into a string

[James Martin]

I have a table that has a field in it called License. This field is text. It
is not required and duplicates are allowed. So, for example, the data might
look like this:

Client License
1 AB12345
2
3
4 CD321
5 AB12345
6
7 EF4444

What I need to do is combine the values from the License field into one
string, ignoring the blank entries. Ideally the string would only contain
one instances of any licenses that are duplicated, but that isn't essential
at this point. So, what I would really like at the end is to get a string
with the value "AB12345,CD321,EF4444". But I'd also be fine with
"AB12345,CD321,AB12345,EF4444".

One option is to use code to read in each record and add any non-null
license values to my string. But I was wondering if anyone knew a simpler
way.

Thanks in advance!

James

 

[Jeff L]

Sounds like a good way to me. If you wanted to get rid of duplicates
and null values your query would be
Select Distinct License

From YourTableName

Where License Is Not Null

Hope that helps!

 

[Duane Hookom]

I you want to concatenate multiple values of License values into a single
expression, use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The
expression inside the function would be something like
Concatenate("SELECT DISTINCT License FROM tblNoNameGiven WHERE License Is
Not Null")

 

[James Martin]

Thanks, Duane. That's perfect!

I you want to concatenate multiple values of License values into a single
expression, use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The
expression inside the function would be something like
Concatenate("SELECT DISTINCT License FROM tblNoNameGiven WHERE License Is
Not Null")