Hi,

I have the below data.

Col1 Col2 Col3 Col4 Col5
------------------------------------------------
CompA Sales Dept UK
CompA Sales Dept USA
CompA Sales Dept Asia
CompB HR Dept USA
CompB HR Dept UK
------------------------------------------------

I am trying to concatenate the above records to get the below result.
Can I use the concatenation function in a query somehow ? Can somebody please point me in the right direction.

------------------------------------------------
CompA Sales Dept USA UK Asia
CompB HR Dept USA, UK
------------------------------------------------

Thanks

Can somebody please guide me on how to do it.

Thanks

Wow, this shows up a lot. This is the result of improper table / relationship design. I have done this with code if you are interested in that solution.

mmcdonal

Look it up at: http://wrox.books24x7.com

Hi,

This data is not directly from a table. This is a query result from various tables. And I am not able to get it in the format I want (basically one row for one company).

Is there a way of querying to get it directly in the required format.

Thanks

You will need to post the table structure. Show some sample data in each table.


mmcdonal

Look it up at: http://wrox.books24x7.com

Hi mmcdonal,

Is there a function to concatenate data from all fields without having to specify the actual field name ?

Thanks

You can write code to refer to columns in recordsets as Column 0, Column 1, etc. It is sometimes easier to use actual column names. But without knowing what the underlying data structure is, there is no way to write code or queries to concatenate this the way you want.

mmcdonal

Look it up at: http://wrox.books24x7.com

Here's some code I sometimes use to work with tables:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()

With db.TableDefs(SourceTable)
    For i = 0 To .Fields.Count - 1
        fieldName(i) = .Fields(i).Name
    Next i
End With

You can then reference the fields like this:

      rs(fieldName(i))

      -Phil-

Pardon me, but that's silly.

There will be *NO DIFFERENCE* between
    rs(fieldName(i))
and simply (and faster)
    rs(i)

What's the point?

Certainly I am suitably chastised for my silliness. Obviously there can be no reason for having a table's field names stored in an array.


      -Phil-