0

I have a database with a table with the following 3 columns of data:

WORKORDER  -  SEQNBR  -  COMMENT
R12345           1       This work was performed on Dec 3
R12345           2       1999 and resulted in the removal
R12345           3       or all similar pumps. All pumps 
R12345           4       have been replace or repair

I'm looking to combine or concatnate all of the COMMENTS for a particular WORKORDER into one column or report text box

Ex: R12345 "This work was performed on Dec 3 1999 and resulted in the removal or all similar pumps. All pumps have been replace or repair.

Is this possible? Any help would be greatly appreciated!

Software Engineer
  • 15,457
  • 7
  • 74
  • 102
B ROB
  • 1
  • 1
    Hi, and welcome to SO. Please read [ask]. Then add some sql to show us what you've tried and how it failed. – Software Engineer Dec 01 '14 at 20:47
  • 1
    There are a number of answers on SO, here is one http://stackoverflow.com/questions/92698/combine-rows-concatenate-rows/93863#93863. They all have problems in that they do not work outside of MS Access. – Fionnuala Dec 01 '14 at 20:48
  • SEQNBR is always 1 to 4 or it may change ? – M. Page Dec 01 '14 at 20:52

1 Answers1

0

MS-Access (and SQL Server) are a pain when it comes to concatenate data of a group. As said by others, there is no good solution for this problem in MS-Access.

Here is however something that will give you what you expect on your example. It assumes that you have a limited number of elements in your sequence (here, I supposed 4). It's ugly, but sometimes, it's necessary to be humble.

SELECT t1.WORKORDER & " " & t1.COMMENT & " " & t2.COMMENT & " " & t3.COMMENT & " " & t4.COMMENT AS Result
FROM ((your_table AS t1 LEFT JOIN your_table AS t2 ON t1.WORKORDER = t2.WORKORDER) 
LEFT JOIN your_table AS t3 ON t2.WORKORDER = t3.WORKORDER) 
LEFT JOIN your_table AS t4 ON t3.WORKORDER = t4.WORKORDER
WHERE t1.SEQNBR=1 
AND t2.SEQNBR=2 
AND t3.SEQNBR=3 
AND t4.SEQNBR=4

From this you get one record with one field containing:

R12345 This work was performed on Dec 3 1999 and resulted in the removal or all similar pumps. All pumps have been replace or repair

M. Page
  • 2,694
  • 2
  • 20
  • 35