M
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.
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.