combine multiple records in a single (1 Viewer)

[r621]

Dears , I need one time again your help, I've a table as following:

TAGNo	INSTRUMENT TYPE	Sh
99-PP-3001	ALARM	33
99-PP-3002	ALARM	33
99-PP-3003	ALARM	33
99-PP-3004	ALARM	33
99-PP-3005	ALARM	33
99-PP-3006	ALARM	33
99-PP-3007	ALARM	33
99-PP-3008	ALARM	33

I need to combine all the records in a single record. I've tried with INNER JOIN but is not working as I want, the common point for all records is the column Sh value 33 which is my sheet number :
Basically I've to combine all records as following (just a sample I've not reported all records)

99-PP-3002

ALARM

33

99-PP-3003

ALARM

33

99-PP-3004

ALARM

33

Could you please help me

 

[theDBguy]

Not sure but this might help?

Code Snippets

 

[Josef P.]

Would a pivot table with column numbers 1-n be possible?

like:

TRANSFORM Max(TAGNo) AS MaxTAGNo
SELECT
     sh, [INSTRUMENT TYPE]
FROM (
      SELECT
             TagNo, [INSTRUMENT TYPE], sh,
             DCount("*","YourTable","sh = " & [sh] & " and TagNo <= '" & [TagNo] & "'") AS N
      FROM
            YourTable
   )  AS Q
GROUP BY sh, [INSTRUMENT TYPE]
PIVOT N

 

[plog]

What's the big picture? What will your data in this format allow you to achieve? Perhaps there is a better way to achieve that end goal.

 

[r621]

Hello Plog, I shall have a single record because I need to import that format in program for automatic drawing

 

[Pat Hartman]

Do you mean export? The data is already in the correct structure in the table you posted. Are you trying to export the normalized data into a one line format for the use of a different application? Look here for the concat function. That is a VBA procedure that you can modify that will read a table and create a string that contains data from multiple rows in the table.

 

[June7]

theDBGuy provided the simplest record concatenation code I've seen. If you want another example, review http://allenbrowne.com/func-concat.html

However, seems to me the output should not repeat ALARM and 33 values. Why not showing TAG_No 99-PP-3001?