Break into individual records (1 Viewer)

[ScottXe]

I have a table consisting multi items in two fields and looking for a way to break them into individual records. They look something like following example.

Co A 123456, 234567 AM12345, BM12345 JB
Co B 345678, 456789 CM45678, DM738738 KL
Co C 567890 EM78968 MM

I would like to output the following format

Co A 123456 AM12345 JB
Co A 234567 BM12345 JB
Co B 345678 CM45678 KL
Co B 456789 DM738738 KL
Co C 567890 EM78968 MM

Your guidance is appreciated.

 

[CJ_London]

if this is how you are storing data, it is not normalised, although your output is so hopefully your requirement is to normalise the data

easiest option is a union query

select fld1, fld2,fld4, fld6 from myTable
union select flf1, fld3,fld5, fld6 from myTable

 

[arnelgp]

you did not label which one is field1 and field2 in your post.

 

[CJ_London]

@arnelgp
not sure if you are referring to me or the OP

 

[arnelgp]

to the op sir, he only have csv there, not sure which one is field1 or field2.

 

[ScottXe]

Sorry for my unclear examples. They should look as below:-

Co SKU Model Incharge
Co A 123456, 234567 AM12345, BM12345 JB
Co B 345678, 456789 CM45678, DM738738 KL
Co C 567890 EM78968 MM

I would like to output the following format

Co SKU Model Incharge
Co A 123456 AM12345 JB
Co A 234567 BM12345 JB
Co B 345678 CM45678 KL
Co B 456789 DM738738 KL
Co C 567890 EM78968 MM

Actually there are 4 fields. I need to break two fields in the table that originally constructed in this way. However, we need the data for other purpose.

 

[Uncle Gizmo]

Please show your question using this format:-

Fld1 ..... Fld2 ..... Fld3 ..... Fld4
A1 ........ A2 ........ A3 ........ A4
B1 ........ B2 ........ B3 ........ B4
C1 ........ C2 ........ C3 ........ C4

Now develop the results based on the above. It should then be clearer what you want.




Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

Oh, I should mention A, B, C, D are row numbers.

Indicate your new row numbers with a lower case letter.

aA1, bB2 .... etc

Sent from my SM-G925F using Tapatalk

 

[ScottXe]

Sorry for confused data in my early message. My table has been stripped down after I submitted the message. Now I attach a excel table for easier understanding.

 

[stopher]

If you are just doing this as a one-off then you can do it pretty quickly in Excel using the "Text to columns" tool.

Otherwise I attach an example of how to do it in Access. My example assumes the fields to be split are simple text delimited by comma/space and that you are not using "multivalued" fields. It assume the delimiter is specifically a comma followed by a space.

 

[arnelgp]

if this is a multivalue fields you can query using the Value of that field:

select Co, SKU.Value, Model.Value, InCharge From youTable;

otherwise you will need a temporary table and a udf to do it.

 

[CJ_London]

based on the number of columns you would still use a union query I suggest originally, just split to the left of the comma for one row and right for the other, plus a criteriea for no right data

SELECT 
     Co
     , iif(instr(SKU,",")=0, SKU,left(SKU,Instr(SKU,",")-1)) AS splitSKU
     , iif(instr(Model,",")=0, Model,left(Model,Instr(Model,",")-1)) AS splitModel
     , InCharge 
FROM myTable
UNION SELECT 
     Co
     , iif(instr(SKU,",")=0, "",mid(SKU,Instr(SKU,",")+1)) AS splitSKU
     , iif(instr(Model,",")=0, "",mid(Model,Instr(Model,",")+1)) AS splitModel
     , InCharge 
FROM myTable 
WHERE
     instr(SKU,",")<>0

 

[arnelgp]

i will go for mr cj london's suggestion.
only this time using a function to do the breaking.
copy and paste the code in a module (if there is non the insert new module).

Public Function fnBreakField(field1 As Variant, _
                            field2 As Variant, _
                            bolRetFirst As Boolean, _
                            delim As String, _
                            position As Integer) As Variant
Dim v1 As Variant
Dim v2 As Variant
v1 = Split(field1, delim)
v2 = Split(field2, delim)
If UBound(v1) >= position - 1 Then
    If bolRetFirst Then
        fnBreakField = Trim(v1(position - 1))
    Else
        fnBreakField = Trim(v2(position - 1))
    End If
End If
End Function

using mr.cj london's union:

SELECT CO, SKU1 AS SKU, MODEL1 AS MODEL, INCHARGE FROM 
(SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",1) AS SKU1, fnBreakField([SKU],[MODEL],False,",",1) AS MODEL1, tblBreak.INCHARGE
FROM tblBreak
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",2) AS SKU1, fnBreakField([SKU],[MODEL],False,",",2) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",3) AS SKU1, fnBreakField([SKU],[MODEL],False,",",3) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",4) AS SKU1, fnBreakField([SKU],[MODEL],False,",",4) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",5) AS SKU1, fnBreakField([SKU],[MODEL],False,",",5) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK) WHERE (SKU1 & "")<>""

replace tblBreak there with the correct tablename. i assume you gave the correct fieldname from previous post.

 

[ScottXe]

i will go for mr cj london's suggestion.
only this time using a function to do the breaking.
copy and paste the code in a module (if there is non the insert new module).

Public Function fnBreakField(field1 As Variant, _
                            field2 As Variant, _
                            bolRetFirst As Boolean, _
                            delim As String, _
                            position As Integer) As Variant
Dim v1 As Variant
Dim v2 As Variant
v1 = Split(field1, delim)
v2 = Split(field2, delim)
If UBound(v1) >= position - 1 Then
    If bolRetFirst Then
        fnBreakField = Trim(v1(position - 1))
    Else
        fnBreakField = Trim(v2(position - 1))
    End If
End If
End Function

using mr.cj london's union:

SELECT CO, SKU1 AS SKU, MODEL1 AS MODEL, INCHARGE FROM 
(SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",1) AS SKU1, fnBreakField([SKU],[MODEL],False,",",1) AS MODEL1, tblBreak.INCHARGE
FROM tblBreak
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",2) AS SKU1, fnBreakField([SKU],[MODEL],False,",",2) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",3) AS SKU1, fnBreakField([SKU],[MODEL],False,",",3) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",4) AS SKU1, fnBreakField([SKU],[MODEL],False,",",4) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK
UNION
SELECT tblBreak.CO, fnBreakField([SKU],[MODEL],True,",",5) AS SKU1, fnBreakField([SKU],[MODEL],False,",",5) AS MODEL1, tblBreak.INCHARGE
FROM tblBreaK) WHERE (SKU1 & "")<>""

replace tblBreak there with the correct tablename. i assume you gave the correct fieldname from previous post.

Hi arnelgp, following your instructions, I made it work correctly with my sample file. Thanks a lot! I would like to apply to my database that has 14 fields instead of 4 fields. How can I extend your code to 14 fields?

 

[arnelgp]

in my union query i only have 5, so modify and add the remaining 9 fields just copy and paste the union select query and renumber them.

 

[ScottXe]

Thanks for your further advice and will try it on.

 

[ScottXe]

Hi arnelgp,

Following your instructions, I created another query with true data. However when I ran it, it showed an error message "Run-time error '9': Subscript out of range". Upon checking the statement, I did not find any errors. Could you please help see if my modifications fit for such real data or where I need to update before it works correctly. Thanks!

 

[arnelgp]

see tblMultiSKU at ID=125, you will see that the Models are not comma separated, but instead space.

try fixing the field by separating with comma and run the query.

 

[ScottXe]

see tblMultiSKU at ID=125, you will see that the Models are not comma separated, but instead space.

try fixing the field by separating with comma and run the query.

Thanks for your expert eyes to spot out the data entry error.

Missing comma in data will halt program running. What else?. Is it possible to show error in the result where contains erratum data? It is more easier to spot the error. What sort of data validation I must check to ensure the program running correctly?

 

[arnelgp]

to be honest with you, a long table would be a good choice.
each sku and model in their own record, if you know what i mean.
this will ease you of the headache to come.