Concatenate Rows

[mp80237]

I have a table (below) and I need to concatenate the rows. I am using Microsoft 2010. I had something that was working but whent the company upgraded the query no longer worked. Below is the table.

thenumber number record_number description type
001A17070186 IM260201 1 Customer reported that they were receiving numerouscalls from agents who were being Executive Summary
001A17070186 IM260201 2 given a message to call security rather than beinggiven the template used to Executive Summary
001A17070186 IM260201 3 clear inhibited passengers. The problem was attributed to Information outages Executive Summary
001A17070186 IM260201 4 which occurred on 24JUN. These outgages prevented correct updating of security . Executive Summary


I need the outcome to look like this:
IM260201 Customer reported that they were receiving numerous calls from agents who were being given a message to call security rather than being given the template used to clear inhibited passengers. The problem was attributed to Information outages which occurred on 24JUN. These outgages prevented correct updating of security .

and I need only the records that have type "Executive Summary". Again I did have something before that was working, but is no longer. I have searched the net and cannot find a working query. Please help.

 

[Jon Lewis]

You need a VBA function to do the concatenation, which you then use in your
query.

Have a look at this:
http://stackoverflow.com/questions/...nse-multiple-lines-in-a-table/5174843#5174843
which uses the ADO GetString method in the VBA function so you will need to
add a ADO library reference if you haven't got it already.

Alternatively you could write your own function using DAO to do the concat.

Why is the data in this format in the first place? If it's because the
description field is a Text field (limited to 255 characters), then change
it to Memo and all the text could go in the one field.

Jon



I have a table (below) and I need to concatenate the rows. I am using
Microsoft 2010. I had something that was working but whent the company
upgraded the query no longer worked. Below is the table.

thenumber number record_number description type
001A17070186 IM260201 1 Customer reported that they were receiving numerous
calls from agents who were being Executive Summary
001A17070186 IM260201 2 given a message to call security rather than being
given the template used to Executive Summary
001A17070186 IM260201 3 clear inhibited passengers. The problem was
attributed to Information outages Executive Summary
001A17070186 IM260201 4 which occurred on 24JUN. These outgages prevented
correct updating of security . Executive Summary


I need the outcome to look like this:
IM260201 Customer reported that they were receiving numerous calls
from agents who were being given a message to call security rather than
being given the template used to clear inhibited passengers. The problem
was attributed to Information outages which occurred on 24JUN. These
outgages prevented correct updating of security .

and I need only the records that have type "Executive Summary". Again I did
have something before that was working, but is no longer. I have searched
the net and cannot find a working query. Please help.

 

[Jon Lewis]

I meant ..."all the text could go in one record"


I have a table (below) and I need to concatenate the rows. I am using
Microsoft 2010. I had something that was working but whent the company
upgraded the query no longer worked. Below is the table.

thenumber number record_number description type
001A17070186 IM260201 1 Customer reported that they were receiving numerous
calls from agents who were being Executive Summary
001A17070186 IM260201 2 given a message to call security rather than being
given the template used to Executive Summary
001A17070186 IM260201 3 clear inhibited passengers. The problem was
attributed to Information outages Executive Summary
001A17070186 IM260201 4 which occurred on 24JUN. These outgages prevented
correct updating of security . Executive Summary


I need the outcome to look like this:
IM260201 Customer reported that they were receiving numerous calls
from agents who were being given a message to call security rather than
being given the template used to clear inhibited passengers. The problem
was attributed to Information outages which occurred on 24JUN. These
outgages prevented correct updating of security .

and I need only the records that have type "Executive Summary". Again I did
have something before that was working, but is no longer. I have searched
the net and cannot find a working query. Please help.

 

[Jon Lewis]

I meant ..."all the text could go in one record"


I have a table (below) and I need to concatenate the rows. I am using
Microsoft 2010. I had something that was working but whent the company
upgraded the query no longer worked. Below is the table.

thenumber number record_number description type
001A17070186 IM260201 1 Customer reported that they were receiving numerous
calls from agents who were being Executive Summary
001A17070186 IM260201 2 given a message to call security rather than being
given the template used to Executive Summary
001A17070186 IM260201 3 clear inhibited passengers. The problem was
attributed to Information outages Executive Summary
001A17070186 IM260201 4 which occurred on 24JUN. These outgages prevented
correct updating of security . Executive Summary


I need the outcome to look like this:
IM260201 Customer reported that they were receiving numerous calls
from agents who were being given a message to call security rather than
being given the template used to clear inhibited passengers. The problem
was attributed to Information outages which occurred on 24JUN. These
outgages prevented correct updating of security .

and I need only the records that have type "Executive Summary". Again I did
have something before that was working, but is no longer. I have searched
the net and cannot find a working query. Please help.

 

[mp80237]

I am tapping into a SQL database. For some reason they have the fields to all of the notes setup that way. I have read only access to it and cannot create views.