Split delimited field data TWICE to populate specific other fields

[MyHarisAlmostGone]

I have tried several variations of doing this in staged queries or outside
of Access in Excel and running import/export through VBA to accomplish the
goal, but keep running into hurdles.

I have one field that contains multiple cost breaks for products, all comma
delimited. So, for instance, Product 321's Cost field will have a cost
breakdown of 1/$5, 10/$45, 50/$200, (this is all in one field; so this
field's data is saying that ProductID 321 can be bought 1 for $5, 10 for
$45, 50 for $200. The cost breaks are always the quantity, a "/", and then
the dollar amount WITH the dollar sign. Each break is always followed by a
comma and space, even the last one). What I need to do is parse out all the
prices and populate a different table's field that corresponds to the
quantity of each price break. In other words, the new table will have
fields ProductID, CostFor1, CostFor10, CostFor50, etc.

Each product can have different numbers of breaks, and they are not always
the same quantity per break either. My external table already has all the
permutations of quantities, though (Fields are ProductID, CostFor1,
CostFor3, CostFor5, CostFor10, CostFor15, CostFor20, CostFor25, CostFor35,
CostFor50, CostFor75, CostFor100). These are the only breaks we have, so no
ProductID's Cost field will be outside of any of these possible values.
However, they can all have different breaks AND different quantities of
breaks. So:

Table tblCosts has:

ProductID Cost
321 1/$25
322 10/$15, 50/$62.50, 100/$100
323 10/$25, 75/$150
324 5/$5, 10/$9.50, 50/$42, 100/$75

So my NEW table, tblCostsSeparated, will have:

ProductID Per1 Per3 Per5 Per10 Per15 Per20 Per25
Per35 Per50 Per75 Per100
321 25
322 15
62.50 100
323 25
150
324 5 9.50
42 75

As you can see, I don't need the dollar signs carried over. In fact, I'd
prefer NOT to have them in my new table.

I have tried using Split on the Cost field and looping through the array,
checking what the value BEFORE each "/" is, and placing the values AFTER
each "/" to put the number in the right field of the new table, but
something about the complication of all this has NONE of my vba working.
I've also tried splitting twice (first using the comma as a delimiter, then
the slash). I think I might be overcomplicating this for myself, but I also
might be trying to use too simple a solution.

Can someone help me accomplish this? I would appreciate the help.

 

[John Spencer]

A better table structure would be three fields like the following.
ProductID
Quantity
Cost

Then you would have records like the following where / represents break
between fields
321 / 1 / 25
322 / 10 /15
322 /50 /62.5
322 / 100 / 100

With that structure you could use something along the lines of this UNTESTED
EXAMPLE AIRCODE


Public Sub xxxx(strProductID, strCost)
Dim x as Variant
Dim I as Long
Dim strSQL as String
Dim iQuantity as Long
Dim cCost as Currency
Dim db as DAO.Database

Set db = CurrentDb()

X = Split(strCost, ",")

For I = Ubound(X) to LBound(X)
IF Len(x(i) & "") > 0 then
iQuantity = Val(x(i))
cCost = Val(Mid(x(i),Instr(x(i),"$")+1))
strSQL = "INSERT INTO tblCostsSeparated (ProductID,Quantity,Cost)" & _
" Values("& strProductID " & "," & iQuantity & "," & cCost& ")"
db.Execute strSQL, dbFailOnError
End if
next I

End Sub

If you must keep your tblCostsSeparated as you proposed, you would need to
build the sql string to include the various fields Per1 etc.

Something like the following in the loop
For I = Ubound(X) to LBound(X)
IF Len(x(i) & "") > 0 then

strInFields = ", PER" & Val(X(i)) & strInFields
strValues = ", " & Val(Mid(x(i),Instr(x(i),"$")+1)) & strValues
end if
Next I

strInfields = Mid(StrInfields,2)
strValues = mid(strValues,2)
strSQL = "INSERT Into tblCostsSeparated (" & strINfields & ")" &
" Values(" & strValues & ")"
db.Execute strSQL, dbFailOnError

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

I agree that multiple fields are a little better than multiple values in a
single field. But this is not much better. I would recommend a table
structure like:

ProductID Per Cost
321 1 25
322 10 15
322 50 62.50
--- etc ---

You can parse out values using a small user-defined function. Copy the
function below and paste it into a new, blank module and save the module as
"modStringFunctions".

You can then use expressions in a query (or code) like:

Qty1: SplitString(splitstring([Cost],",",1),"/$",1)
Price1: SplitString(splitstring([Cost],",",1),"/$",2)
Qty2: SplitString(splitstring([Cost],",",2),"/$",1)
Price2: SplitString(splitstring([Cost],",",2),"/$",2)


Function SplitString(strIn As String, strDelim As String, _
intPart As Integer, Optional booTrim As Boolean = True) As String
Dim Ary
'Arrays are zero based
intPart = intPart - 1
Ary = Split(strIn, strDelim)
If intPart >= 0 And intPart <= UBound(Ary) Then
If booTrim Then
SplitString = Trim(Ary(intPart))
Else
SplitString = Ary(intPart)
End If
Else
SplitString = ""
End If
End Function

 

[MyHairIsAlmostGone]

I ALSO agree about the bad structure, but I'm working with some legacy db's
that I don't have the option of normalizing, or even really changing. Thus,
I do a lot of exporting, wrangling, then re-importing.

Thanks to the both of you. Duane, your solution worked flawlessly, and I'm
surprised that as I wrote the subject line for the post to begin with that
it didn't occur to me to nest a split (I even wrote "use split twice"); it
was one thing I didn't try, but it still wouldn't have worked without your
function. Thanks again to both of you, much appreciated.

I agree that multiple fields are a little better than multiple values in a
single field. But this is not much better. I would recommend a table
structure like:

ProductID Per Cost
321 1 25
322 10 15
322 50 62.50
--- etc ---

You can parse out values using a small user-defined function. Copy the
function below and paste it into a new, blank module and save the module
as
"modStringFunctions".

You can then use expressions in a query (or code) like:

Qty1: SplitString(splitstring([Cost],",",1),"/$",1)
Price1: SplitString(splitstring([Cost],",",1),"/$",2)
Qty2: SplitString(splitstring([Cost],",",2),"/$",1)
Price2: SplitString(splitstring([Cost],",",2),"/$",2)


Function SplitString(strIn As String, strDelim As String, _
intPart As Integer, Optional booTrim As Boolean = True) As String
Dim Ary
'Arrays are zero based
intPart = intPart - 1
Ary = Split(strIn, strDelim)
If intPart >= 0 And intPart <= UBound(Ary) Then
If booTrim Then
SplitString = Trim(Ary(intPart))
Else
SplitString = Ary(intPart)
End If
Else
SplitString = ""
End If
End Function

--
Duane Hookom
Microsoft Access MVP

I have tried several variations of doing this in staged queries or
outside
of Access in Excel and running import/export through VBA to accomplish
the
goal, but keep running into hurdles.

I have one field that contains multiple cost breaks for products, all
comma
delimited. So, for instance, Product 321's Cost field will have a cost
breakdown of 1/$5, 10/$45, 50/$200, (this is all in one field; so this
field's data is saying that ProductID 321 can be bought 1 for $5, 10 for
$45, 50 for $200. The cost breaks are always the quantity, a "/", and
then
the dollar amount WITH the dollar sign. Each break is always followed by
a
comma and space, even the last one). What I need to do is parse out all
the
prices and populate a different table's field that corresponds to the
quantity of each price break. In other words, the new table will have
fields ProductID, CostFor1, CostFor10, CostFor50, etc.

Each product can have different numbers of breaks, and they are not
always
the same quantity per break either. My external table already has all
the
permutations of quantities, though (Fields are ProductID, CostFor1,
CostFor3, CostFor5, CostFor10, CostFor15, CostFor20, CostFor25,
CostFor35,
CostFor50, CostFor75, CostFor100). These are the only breaks we have, so
no
ProductID's Cost field will be outside of any of these possible values.
However, they can all have different breaks AND different quantities of
breaks. So:

Table tblCosts has:

ProductID Cost
321 1/$25
322 10/$15, 50/$62.50, 100/$100
323 10/$25, 75/$150
324 5/$5, 10/$9.50, 50/$42, 100/$75

So my NEW table, tblCostsSeparated, will have:

ProductID Per1 Per3 Per5 Per10 Per15 Per20
Per25
Per35 Per50 Per75 Per100
321 25
322 15
62.50 100
323 25
150
324 5 9.50
42 75

As you can see, I don't need the dollar signs carried over. In fact, I'd
prefer NOT to have them in my new table.

I have tried using Split on the Cost field and looping through the array,
checking what the value BEFORE each "/" is, and placing the values AFTER
each "/" to put the number in the right field of the new table, but
something about the complication of all this has NONE of my vba working.
I've also tried splitting twice (first using the comma as a delimiter,
then
the slash). I think I might be overcomplicating this for myself, but I
also
might be trying to use too simple a solution.

Can someone help me accomplish this? I would appreciate the help.


.