is there a Way to Combine Different Values for a field?

[rfan]

Hi all,
Is there a Way to Combine Different Values in a field as a ONE value field?
Any suggestions are appreciated.


Thanks in advance.

 

[boblarson]

In a query you can:

MyNewFieldName:[YourFieldName1] & [YourFieldName2] ...etc.

 

[tehNellie]

Yes,
Fullname: [firstname] & " " & [Surname]

in the query Builder

Or

SELECT [firstname] & " " & [Surname] AS Fullname

In SQL view.

 

[neileg]

The above advice is correct. I'm just not sure the question is that clear

What do you mean by "Different Values in a field"? Have jou got one field with a string of values, say separated by commas?

 

[rfan]

Now, I have field names "Country", what I want to do is to combine nonempty Distinct Country names.

For example, I have:
Country
USA
NULL
Canada
Russia

I want:
Conutries
USA/Canada/Russia


Is it possible to do that?

 

[boblarson]

While it's possible to do, it's not a good idea to store data that way. In fact it violates the whole reason for relational databases.

 

[rfan]

Well, You are probably right, how to do that then?

 

[boblarson]

First, if you are going to need this frequently, you will probably want to create a function to return the concatenated value for display:
Put this in a STANDARD MODULE (not a form module):

Public Function CountriesConc() As String
    Dim rst As ADODB.Recordset
    Dim strCountries As String

    Set rst = New ADODB.Recordset

    rst.Open "YourTableNameHere", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

    Do Until rst.EOF
        strCountries = strCountries & rst.Fields("Country") & "/"
        rst.MoveNext
    Loop
    strCountries = Left(strCountries, Len(strCountries) - 1)
    CountriesConc = strCountries

    rst.Close
    Set rst = Nothing
End Function

Then you can call that function any time to populate whatever you need.

 

[Cosmos75]

Perhaps this may also be helpful.

DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link).