Merging data in one field from multiple records

[jereece]

I am linking to an SQL database that is designed in such a way that the problem description is recorded in multiple records. See a simplified example below. I want to run a query that will merge the information in the PROBLEM_DESCRIPTION and return only one record for PROB_ID 55678.

PROB_ID ORIGINATOR DATE PROBLEM_DESCRIPTION
55678 Jim 01/01/2015 While cleaning my computer, I noticed
55678 Jim 01/01/2015 the screen was cracked. I called
55678 Jim 01/01/2015 the IT department and they said I had
55678 Jim 01/01/2015 to write a work order. However it
55678 Jim 01/01/2015 was rejected without reason.

As always I appreciate your help.

Jim

 

[Galaxiom]

Do you have another field that records the order the Description segments should appear in?

 

[jereece]

Not sure. I have not looked for a unique field, but you would think there would be one. When I run a query, it always returns the records in order. Would a unique field help solve my problem?

 

[jdraw]

Is prob_id 55687 a typo? Why would it be selected if your looking for 55678?
What is the data type and length of field Problem_Description?

 

[Galaxiom]

When I run a query, it always returns the records in order.

That is lucky. Access does not care about the intrinsic order of records and will sometimes put them in wherever. Keep frequent backups in case this happens.

I would avoid deleting any records and especially avoid compacting that database until another field is added to control the order of the records.

The first priority should be to get that numbering in place. I would try adding an autonumber (incrementing) field to the table and then confirm the records remain in the original order.

Then open a recordset and iterate through it, concatenating the problem description to a variable while the ProbID remained the same. When a new ID is encountered, write a new record to another table with that concatenated text and begin on the next group of records.

 

[jereece]

jdraw - Yes that is a typo. All fields contain the same information except the problem description. I have no clue why this database was created that way. It's an action tracking database developed by ABB company and used by a lot of people in the utility industry.

Galaxiom - I am only linking to the database to develop queries and reports. I cannot modify the database in any way.

Thanks,
Jim

 

[Galaxiom]

I have no clue why this database was created that way. It's an action tracking database developed by ABB company and used by a lot of people in the utility industry.

Sometimes developers avoid using large fields (such as Memo) by spreading out data over multiple records. The example you gave seems a bit extreme though.

I am only linking to the database to develop queries and reports. I cannot modify the database in any way.

Ah the data is not in Access. That is good. The original database must keep the order correct. Not a good practice to leave it to the database though.

A loop through a recordset as I vaguely described above is what you will need to do to concatenate the records.

 

[jereece]

A loop through a recordset as I vaguely described above is what you will need to do to concatenate the records.

Any easy to understand tutorials on how to do this? I am a medium level Access user with no experience in writing code.

Thanks,
Jim