Complex Field Splitting problem

[Plasma101]

OK Guys,
I am building a sales database for a client he has supplied the text files which I have loaded into tables.
Problem on the sales file there are multiple part numbers in the part field these are separated by '-' the same format is on the Qty and value fields. The logic behind this is:
For the 1st part in the part field the qty and the value are the 1st in there respective columns, for the 2nd part in the part field the Qty an value are the 2nd in there respective columns and so on eg


PS001 |BAGR3-BAGR4-BAGR6 |03-01-01 |4.29-5.17-5.99

How would I get above to look like this:


PS001 |BAGR3 |03 |4.29
PS001 |BAGR4 |01 |5.17
PS001 |BAGR6 |01 |5.99

Any suggestions would be great

thanks

Chris

 

[Smart]

in your query you can substring each column using "-" as the delimeter

 

[Plasma101]

Smart,
Yes I can substring each column but that would just give me and extra column and would not create a new line.
Or am I missing somthing ??

Thank Chris

 

[Pat Hartman]

You can't work with unnormalized data like this in a query. You will need to write code in order to append multiple records for each input record.

 

[Smart]

RE : RE: Complex Field Splitting problem

May not be ideal but you could have a make table query that uses your substrings

 

[Plasma101]

Thank for your suggestions,
Looks like i'm going to be busy recoding this.

Cheers

Chris

 

[mresann]

Here's A Start

This is not exactly what you want, but can provide you with the right direction.

This function splits a record line using a delimiter, and returns just the element that is requested. For instance,

fncSplitElement("ABC-DEF-GHI", "-", 1)

returns "DEF" (the first element is always 0)

You can put the fncSplitElement in the Update or Append field of a query in design mode, or in an SQL statement.

NOW, I know this is not what you have exactly, but this is a good general purpose module to have on hand.

Public Function fncSplitElement( _
      ByVal strText As String, _
      ByVal strDelimiter As String, _
      ByVal lngElement As Long) As String
'-----------------------------------------------------
' Procedure : fncSplitElement
' Created   : Jul 20 2005 08:07
' Reference : fncSplitElement*
' Author    : Michael Reese
' Input(s)  :
' Output(s) :
' Purpose   :
'-----------------------------------------------------

PROC_DECLARATIONS:
   Dim strSplitElement   As String
   Dim astrElement()     As String
   Dim lngCounter        As Long
   
PROC_START:
   On Error GoTo PROC_ERROR
   
PROC_MAIN:
   astrElement = Split(strText, strDelimiter)
   strSplitElement = astrElement(lngElement)
   
PROC_EXIT:
   fncSplitElement = strSplitElement
   Exit Function

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: fncSplitElement" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT
                                
End Function

 

[mresann]

This what you are looking for?

I created a function for you.

Call the function (either by itself or assigning the variant return to an array) with your unformatted line of data as an input.

Public Function fncPlasma101(strText As String) As Variant
'-----------------------------------------------------------------------------------------
' Procedure : fncPlasma101
' Created   : Jul 20 2005 08:28
' Reference : fncPlasma101*
' Author    : Michael Reese
' Input(s)  :
' Output(s) :
' Purpose   :
'-----------------------------------------------------------------------------------------

'|<------ 90-character width -------------------------------- 90-character width ------->|

PROC_DECLARATIONS:
   Dim varSplitElement   As Variant
   Dim strSplitElement   As String
   Dim astrElement1()     As String
   Dim astrElement2()     As String
   Dim lngcounter1        As Long
   Dim lngCounter2        As Long
   Dim astrReturn()      As Variant
   Dim lngFieldCount     As Long
      
PROC_START:
   On Error GoTo PROC_ERROR
   
PROC_MAIN:
   astrElement1 = Split(strText, "|")
   'format PS001 |BAGR3-BAGR4-BAGR6 |03-01-01 |4.29-5.17-5.99
   ReDim astrReturn(3, UBound(astrElement1))
   For lngcounter1 = 0 To UBound(astrElement1)
      astrReturn(0, lngcounter1) = Trim(astrElement1(0))
      astrElement2 = Split(astrElement1(lngcounter1), "-")
      For lngCounter2 = 0 To UBound(astrElement2)
         astrReturn(lngcounter1, lngCounter2) = Trim(astrElement2(lngCounter2))
      Next
   Next

PROC_EXIT:
   fncPlasma101 = astrReturn
   Exit Function

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: fncPlasma101" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT

End Function

NOW, since the return is a VARIANT MULTI-DIMENSIONAL ARRAY, you have to test it as follows:

Sub subPlasma101()
'-----------------------------------------------------------------------------------------
' Procedure : subPlasma101
' Created   : Jul 20 2005 09:06
' Reference : subPlasma101*
' Author    : Michael Reese
' Input(s)  :
' Output(s) :
' Purpose   :
'-----------------------------------------------------------------------------------------

'|<------ 90-character width -------------------------------- 90-character width ------->|

PROC_DECLARATIONS:
   Dim astrPlasma101()   As Variant
   Dim lngcounter1      As Long
   Dim lngCounter2      As Long
   Dim varPlasma101     As Variant
   
PROC_START:
   On Error GoTo PROC_ERROR
   
PROC_MAIN:
   varPlasma101 = fncPlasma101("PS001 |BAGR3-BAGR4-BAGR6 |03-01-01 |4.29-5.17-5.99")
   astrPlasma101 = varPlasma101
   For lngcounter1 = 0 To 2
      For lngCounter2 = 0 To 3
         Debug.Print astrPlasma101(lngCounter2, lngcounter1) & ", ";
      Next
      Debug.Print
   Next
   
PROC_EXIT:
   Exit Sub

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: subPlasma101" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT

End Sub

You can probably adjust the array variable or use the InsertRow function as needed. Incidentally, all fields are currently in Text mode, so you may have to change the datatypes before insertion in the tables.

 

[gblack]

Quicky solution

Try taking the data and throwing it into a .txt file.

Then import it back into access.

When you go through the import wizard, choose "other" for the delimiting value, then make delimiter a "-"

That should break your data up into separate fields in your table.

HTH
Gary

Once you have the fields broken up in a table, you can make seperate queries for each of your data sets... then you can stack them via a UNION Query. It might be a little bulky...but you don't have to code...