Split text having comma in different column or rows

[Christykvlklm]

I want to split the text having comma in different rows or columns. This is my sample data. Table name "ztadefect"

Code OrderNo DrawingNo JointNo WelderNo RTResult Defect I100000011930 20011937 P1287CMB-FG-114-3424-12 1 W093 Rej LOP,LF,EP


I need data in below format,

1 2 3 4 5 6 7 Code OrderNo DrawingNo JointNo WelderNo RTResult Defect I100000011930 20011937 P1287CMB-FG-114-3424-12 1 W093 Rej LOP I100000011930 20011937 P1287CMB-FG-114-3424-12 1 W093 Rej LF I100000011930 20011937 P1287CMB-FG-114-3424-12 1 W093 Rej EP

First 6 columns having unique data. Please help me to have split function code for above in access query (Access 2013).

Thanks!
Christy

 

[CJ_London]

it can be done, but how depends on what variation of data you have - a single record doesn't really cover all the options. So a few questions:

is there always 3 codes in your defect column?
If not, what is the min and max number of codes?
Is the data to be stored (i.e. use an append query) or to be viewed only (a select query)?
Is there a separate table of defect codes?
Are defect codes totally different (e.g. there isn't one code LEP and another code LE or EP)

At the moment the easiest option would be to use a Cartesian query, but depends on the variations of data whether this would be successful or not.

 

[Christykvlklm]

it can be done, but how depends on what variation of data you have - a single record doesn't really cover all the options. So a few questions:

is there always 3 codes in your defect column?
If not, what is the min and max number of codes?
Is the data to be stored (i.e. use an append query) or to be viewed only (a select query)?
Is there a separate table of defect codes?
Are defect codes totally different (e.g. there isn't one code LEP and another code LE or EP)

At the moment the easiest option would be to use a Cartesian query, but depends on the variations of data whether this would be successful or not.

Thanks CJ for Immediate Reply.

I have Separate table for Defect code.
But the against the data that posted one record may have different defects, that user will update using comma. I attached defect code list what I have in separate table (tbldefectcode)

 

[CJ_London]

OK, I think a cartesian query will work - substitute table names in red as required

 SELECT *
 FROM [COLOR=red]tblOrders[/COLOR] O, [COLOR=red]tblDefects[/COLOR] D
 WHERE InStr("," & replace([O].[Defect]," ","") & ",","," & [D].[Defect] & ",")>0
 ORDER BY O.Code

basically it is surrounding the codes with commas e.g.

LOP,LF,EP becomes ,LOP,LF,EP,

and defect EP becomes ,EP,

so ,EP,

can be found in

,LOP,LF,EP,

The reason for including the replace function is in case your users have entered spaces after commas. If they don't do that you can remove the replace function

Note this will not return any records where there is a code in tblOrders, but not in tblDefects

 

[Christykvlklm]

The query working, but output not what I expecting. Master table is "ztadefect". In that defect description having with comma need to be in separate columns or different rows with corresponding same row data.

I attached the file.

Thanking You.
Christy

 

[CJ_London]

but output not what I expecting

Not sure what you mean. I corrected for the field name and the missing field name in the order by

Just copy and paste this

SELECT *
FROM ztadefect AS O, tblDefectcode AS D
WHERE (((InStr("," & Replace([O].[Defect]," ","") & ",","," & [D].

 & ","))>0))
ORDER BY O.code

 I used SELECT * for brevity and assume you would replace the *with the fields you actually want (Only difference is the inclusion of the ztadefect.Defect and tblDefectcode.CodeDescription columns)

As previously mentioned, this will not bring through records with 

[EMAIL="PORE@42"]PORE@42[/EMAIL] LEAD LETTER ON WELD

as a defect code and this code I00000001620 has these defects LF,SI,P, but SI is not in your tblDefectCodes table

So from your initial brief requirement the query is doing as expected.

 

[Christykvlklm]

This What I need. Data mismatching I will correct it. Thank you for your time with us.

Again Thanking You,

Christy

 

[CJ_London]

happy to help!