Trim Data In MS Access

The first requirement is easy. The second required a user-defined function. Paste the code below in a new module, and please try this query:

SELECT *, Left(Info, InStr(Info, ',') - 1) As Info1, Alpha(Info) As Info2
FROM <your table name here>

Good luck!
(°v°)

Function Alpha(pvarMix)
    
    Dim strC As String
    Dim i As Integer
    
    Alpha = Null
    If IsNull(pvarMix) Then Exit Function
    For i = 1 To Len(pvarMix)
        strC = Mid(pvarMix, i, 1)
        If strC Like "[A-Z]" Then
            Alpha = Alpha & strC
        Else
            Exit Function
        End If
    Next i
    
End Function

Select allOpen in new window

Sorry, I reread and understood your requirement better. This will not be easy at all, and I don't think it can be done in a single query. You will have to generate a new table, using a VB function. See below to fill the new table.

Note: untested "air code". Please adjust as necessary.

Good luck!
(°v°)

Option Compare Database
Option Explicit
 
Function Alpha(pvarMix)
    
    Dim strC As String
    Dim i As Integer
    
    Alpha = Null
    If IsNull(pvarMix) Then Exit Function
    For i = 1 To Len(pvarMix)
        strC = Mid(pvarMix, i, 1)
        If strC Like "[A-Z]" Then
            Alpha = Alpha & strC
        Else
            Exit Function
        End If
    Next i
    
End Function
 
Sub FillTable()
 
    Dim recOri As DAO.Recordset
    Dim recNew As DAO.Recordset
    Dim InfoPart() As String
    Dim i As Integer
    
    Set recOri = CurrentDb.OpenRecordset("OLD_TABLE")
    Set recNew = CurrentDb.OpenRecordset("NEW_TABLE")
    
    Do Until recOri.EOF
        InfoPart = Split(recOri!Info, ",")
        For i = 0 To UBound(InfoPart)
            recNew.AddNew
            recNew!Data1 = recOri!Data1
            recNew!Data2 = recOri!Data2
            recNew!Info = recOri!Info
            recNew!Info2 = InfoPart(i)
            recNew!info3 = Alpha(InfoPart(i))
            recNew.Update
        Next i
        recOri.MoveNext
    Loop
 
End Sub

Select allOpen in new window

Hi Harfang

Thanks a million for the quick respond.
Seems that i have underestimate the 'easy' question :-)
Please bear with me as i am no good with VB and has limited knowledge with MS Access.
I do not quite understand what you mean by: 'You will have to generate a new table, using a VB function. See below to fill the new table.'
What is OLD TABLE and NEW TABLE? Can you help to explain more?

Thanks

OLD_TABLE is the one you have. Copy it, without the data (use the option "structure only" when copying it), and add the two new fields. Then try to run the code I showed, using the correct table names.

If you get a compile error on the line with DAO.Recordset, you need to add the reference to the library:
* From VB, choose (Tools | References)
* Find and select "Microsoft DAO ?.? Object Library"

But perhaps you don't know how to create a module: select the "modules" tab and click [New]. Paste the code into the module, choose (Debug | Compile...) to make sure there are no syntax errors, and then run the sub called FillTable.

It's true: this is more complicated that you would think. Access is not really meant to be used like this, i.e. generating several rows based on comma-separated values. I don't see a query equivalent to this. Well, I do, but it it would end up being even more complicated than the VB solution.

I hope this helps.
(°v°)

Hi Hafang

I get the required data for Info1 but unfortunately, incomplete data for Info2.
Sorry to trouble you.
Kindly advise what i should do.

The result i obtained:
Data1      Data2      Info      Info1      Info2
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX1      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX2      
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX3      
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX4      
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX5      
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX6      
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX7      
B      P1      A2, A3, A4, K8, K9, Y3, Y6      A2      A
B      P1      A2, A3, A4, K8, K9, Y3, Y6      A3      
B      P1      A2, A3, A4, K8, K9, Y3, Y6      A4      
B      P1      A2, A3, A4, K8, K9, Y3, Y6      K8      
B      P1      A2, A3, A4, K8, K9, Y3, Y6      K9      
B      P1      A2, A3, A4, K8, K9, Y3, Y6      Y3      
B      P1      A2, A3, A4, K8, K9, Y3, Y6      Y6      
C      P1      V1, V2, V3, V100, V300, Z9      V1      V
C      P1      V1, V2, V3, V100, V300, Z9      V2      
C      P1      V1, V2, V3, V100, V300, Z9      V3      
C      P1      V1, V2, V3, V100, V300, Z9      V100      
C      P1      V1, V2, V3, V100, V300, Z9      V300      
C      P1      V1, V2, V3, V100, V300, Z9      Z9      
D      P1      U1, U100, U2, U300, U5, U800      U1      U
D      P1      U1, U100, U2, U300, U5, U800      U100      
D      P1      U1, U100, U2, U300, U5, U800      U2      
D      P1      U1, U100, U2, U300, U5, U800      U300      
D      P1      U1, U100, U2, U300, U5, U800      U5      
D      P1      U1, U100, U2, U300, U5, U800      U800      
E      P1      CONN1, EN1, G2, K1000                           CONN1      CONN
E      P1      CONN1, EN1, G2, K1000                           EN1      
E      P1      CONN1, EN1, G2, K1000                           G2      
E      P1      CONN1, EN1, G2, K1000                           K1000      
F      P1      BXX1, FE200, R0, V9, V90      BXX1      BXX
F      P1      BXX1, FE200, R0, V9, V90      FE200      
F      P1      BXX1, FE200, R0, V9, V90      R0      
F      P1      BXX1, FE200, R0, V9, V90      V9      
F      P1      BXX1, FE200, R0, V9, V90      V90      
G      P1      T1, T2, T3, T4, T600                            T1      T
G      P1      T1, T2, T3, T4, T600                            T2      
G      P1      T1, T2, T3, T4, T600                            T3      
G      P1      T1, T2, T3, T4, T600                            T4      
G      P1      T1, T2, T3, T4, T600                            T600      

The dessired result:
Data1      Data2      Info                                                Info1      Info2
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX1      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX2      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX3      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX4      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX5      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX6      EX
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX7      EX
B      P1      A2, A3, A4, K8, K9, Y3, Y6      A2      A
B      P1      A2, A3, A4, K8, K9, Y3, Y6      A3      A
B      P1      A2, A3, A4, K8, K9, Y3, Y6      A4      A
B      P1      A2, A3, A4, K8, K9, Y3, Y6      K8      K
B      P1      A2, A3, A4, K8, K9, Y3, Y6      K9      K
B      P1      A2, A3, A4, K8, K9, Y3, Y6      Y3      Y
B      P1      A2, A3, A4, K8, K9, Y3, Y6      Y6      Y
C      P1      V1, V2, V3, V100, V300, Z9      V1      V
C      P1      V1, V2, V3, V100, V300, Z9      V2      V
C      P1      V1, V2, V3, V100, V300, Z9      V3      V
C      P1      V1, V2, V3, V100, V300, Z9      V100      V
C      P1      V1, V2, V3, V100, V300, Z9      V300      V
C      P1      V1, V2, V3, V100, V300, Z9      Z9      Z
D      P1      U1, U100, U2, U300, U5, U800      U1      U
D      P1      U1, U100, U2, U300, U5, U800      U100      U
D      P1      U1, U100, U2, U300, U5, U800      U2      U
D      P1      U1, U100, U2, U300, U5, U800      U300      U
D      P1      U1, U100, U2, U300, U5, U800      U5      U
D      P1      U1, U100, U2, U300, U5, U800      U800      U
E      P1      CONN1, EN1, G2, K1000                           CONN1      CONN
E      P1      CONN1, EN1, G2, K1000                            EN1      EN
E      P1      CONN1, EN1, G2, K1000                            G2      G
E      P1      CONN1, EN1, G2, K1000                            K1000      K
F      P1      BXX1, FE200, R0, V9, V90                      BXX1      BXX
F      P1      BXX1, FE200, R0, V9, V90                      FE200      FE
F      P1      BXX1, FE200, R0, V9, V90                      R0      R
F      P1      BXX1, FE200, R0, V9, V90                      V9      V
F      P1      BXX1, FE200, R0, V9, V90                      V90      V
G      P1      T1, T2, T3, T4, T600                            T1      T
G      P1      T1, T2, T3, T4, T600                            T2      T
G      P1      T1, T2, T3, T4, T600                            T3      T
G      P1      T1, T2, T3, T4, T600                            T4      T
G      P1      T1, T2, T3, T4, T600                           T600      T

Please note that the dsiplay for obtained result and desired result is a bit messy.
Kindly refer attached file for details.

Thanks again.
Appreciate the help.
Data.doc

Hi Hafang
I have a suggestion:
Since you mentioned that my first requirement is easy (Separate the data in Info to individual data by using delimiter ',' (new field Info2), hen is it possible for me to do the data extraction stage by stage:
1st step:
- Pls advive how to cerate a Query to separate the data in Info to individual data by using delimiter ','
After i get this 1st query result which display the info into multiple rows, eg as below:
Data1      Data2      Info                                             Info1    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX1    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX2    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX3    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX4    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX5    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX6    
A      P1      EX1, EX2, EX3, EX4, EX5, EX6, EX7      EX7    
Step 2:
Then i can easily create another query just to extract the alphabet info for new field Info1 by just adding criteria Alpha([Info1])

Is this idea workable for a beginners like me?

Kindly advice.

Thanks

Thanks so much again. Really appreciate it.

Hi Harfang

The newly adjusted new VB works wonderfully well :-)
Appreciate it so much for the help.
Sorry that the points given is not much :-)

Did you learn VB on your own?
I tried learning once by reading the User Guide.
I can't seems to get a hang of it.
As a result, i am still stuck with simple MS Access query.
Must really try harder to learn again.

Thanks again :-)

Thank you for your kind words.

I did learn BASIC many many years before Visual Basic, so I'm no reference. But I did learn on my own, yes. Using books, help files, and in later years Internet resources. Some people prefer to take classes (and I teach some as well), so if books don't work for you, you can try to find a class in your area.

Success with your learning!
(°v°)