Multiplying subpart quantities

[Guest]

Using Access 2003 on Windows XP. I have a program called "Solidworks" that
exports a list of parts needed for a Job to an excel file. Some of the parts
have "subparts" that need to be ordered. For example, part B22 is made up of
parts B22-1, B22-2, etc. My problem is that the quantities for the subparts
only indicate how many are needed to make one of the main part, regardless of
how many of the main part you want. For example (and all of the fields below
are generated by Solidworks):

Item# Quantity Part# Description
1 2 A12 blah
2 3 B32 dfasdf
3 2 B22 fdd
3.1 1 B22-1 afdgf
3.2 1 B22-2 asdfdf
3.3 2 B22-3 fdadf
4 5 C43 afdhf
5 1 A24 afaaf

Where items 3.1, 3.2, 3.3 are all of the parts needed to make one of item 3
(I hope I am making sense so far). I already have the code and queries that
will import the spreadsheet and cut off the leading spaces for the part
numbers. What I need is to find a way to multiply the quantities of the
subparts to indicate how many I need to make as many of the main part as I
need. So the previous example should read:

Item# Quantity Part# Description
1 2 A12 blah
2 3 B32 dfasdf
3 2 B22 fdd
3.1 2 B22-1 afdgf
3.2 2 B22-2 asdfdf
3.3 4 B22-3 fdadf
4 5 C43 afdhf
5 1 A24 afaaf

Since, in order to make two of item 3, I will need twice as many of the sub
parts. To further complicate things, there can be sub-sub parts (3.1.1,
3.1.2) and sub-sub-sub parts (3.1.2.1, 3.1.2.2. etc) ad infinitum, so all of
the sub part quantities need to be multiplied by the quantities in all of the
parent parts. I really hope that makes sense. Thanks for your expertise,

Adam Milligan (per the 'etiquette' page I am going to change my juvenile
handle as soon as I figure out how.)

 

[Peter Danes]

Hi Adam,

I was hoping some SQL freak would grab this with a brilliant solution, but
doesn't seem to be happening. So here's my two cents.

AFAIK, you can't do this sort of thing with SQL at all. You need to get a
value from a record, then hold that value while investigating subsequent
records to the one from which you got the value. I don't think SQL has that
ability. It might be possible to do some trickery with self-joins to achieve
a pre-determined number of levels, but since you say that the number of
sub-part levels is essentially unlimited, that's not good enough.

However, this sort of problem is tailor-made for a recursive routine. Is VBA
coding an option for your project? If you're not familiar with VBA at all,
recursion is a rather large mouthful for a first bite, although it is
doable. If you're not familiar with the concept, the essential idea is to
pass a parameter to a routine, the routine divides the parameter into two
parts, does something concrete with one part and calls itself with the
remaining, smaller part as a new parameter. That new call again divides the
newer, smaller parameter, does something with one part and passes the
remainder as a newer, even smaller parameter to itself, and so on. At some
point, the parameter is small enough that the routine no longer divides the
parameter, but only does something with the entire parameter, then returns.
That returns up to the previous level, which returns to its previous level
and so on all the way back up the stack.

The classic case is computing N!, where the guts of the routine FACT(N) are
defined as N * FACT(N-1). If you Google the words recursive factorial,
you'll get scads of descriptions, tutorials and code samples. In your case,
you would scan your recordset in VBA, and when you saw that the item number
was a sub-part of the previous record, you would start this process. As each
record revealed it self to be a sub-part of the previous one, you would bump
down one level in the recursion, and when not, you would head back up.

Another approach, which might be simpler than true recursion, is a stack.
VBA doesn't support a stack natively, but it's simple enough to code a
simulation of one, using arrays or collections.

As a first iteration, the flow would be something like this:

Read a record.
If partno is same level as previous partno, multiply by all multipliers on
stack (if any). (3.2 after 3.1, or 5.4.7 after 5.4.6...)
If partno is subpart of previous partno then push multiplier onto stack,
then multiply by all multipliers on stack. (3.2.1 after 3.2 or 5.4.1 after
5.4...)
If partno is higher in level heirarchy than previous partno, pop as many
multipliers off stack as necessary to get to that level (4 after 3.2.6 or
3.3 after 3.2.6 or 6 after 5.4.7 or 5.5 after 5.4.7...)
Loop

Does this give you any ideas?

Pete

 

[Guest]

Peter-

Thanks for the two cents (more like four cents as far as I'm concerned). I
know enough vba to get me by (my most advanced modules involved cycling
through a recordset to generate the body of an e-mail) but this looks way
beyond me. I might try to come up with something and post my progress back
to the vba forum. Again, thanks for your time.

 

[Peter Danes]

Hi Adam,

If you know enough VBA to have done that much, you're a good portion of the
way there. This really wouldn't be that much more difficult. I thought about
it some more after posting last night and decided that the stack approach
would probably be more reasonable than recursion; it's simpler to code and
debug, and it fits the workflow better. Here are a few more tips on how I
would approach the problem; maybe they'll be enough to get you oriented.

First, the stack. Some languages have a native stack mechanism, Forth is the
one with which I am most familiar. It contains commands Push and Pop, most
often compared to a pile of plates in a spring-loaded cafeteria dish holder.
You add plates by putting them on top of the heap, and then when you want a
plate, the last one put on (the top one) is the first one taken off. You may
have seen the acronym LIFO, Last In First Out. VBA doesn't have this, but
you can use an array for similar results. You said that the number of
sublevels is essentially unlimited, but as a practical matter, since you
have a finite number of parts and records, there will be an actual maximum
number of levels that you will encounter. Suppose that you don't
realistically expect more than ten sublevels; then dimension a string array
to one hundred and you should be covered, even if the application grows
considerably. If you expect maybe fifty sublevels, dimension it to five
hundred. Storage is cheap. You will also need a numeric variable to keep
track of how many sublevels deep you are, that will be a pointer to your
array, containing the number of the largest element used at any given time.
If you are dealing with itemno 7.4.2, your variable will be three, if you
are on itemno 7.4.2.9.18.5.32.4, your variable will be 8 and so on.

You will probably want to use two arrays, one string array for the itemno
and one numeric array for the multiplier, the count of items in each one
would be the same, so your numeric variable would simultaneously keep track
of both. There are many coding tricks that might make this faster and more
elegant, but let's keep it simple to begin with. Here are a few samples of
how your array would look at various points in the process:

ItemNo Multiplier
===== ======
3 2
Here you've just read a base level record and stored it in the array. You
don't know what's ahead yet, so you only have the one level stored. The
count is one, the multiplier is two.


ItemNo Multiplier
===== ======
3 2
3.1 5
Here you've picked up one sub-level, so you've advanced the count by one
(pushed another level onto the stack) and added the first sub-level
information. The count is now two, parts multiplier is ten (2*5).


ItemNo Multiplier
===== ======
3 2
3.1 5
3.1.4 6
Here you've added a third level. The count is three, the multiplier is 60
(2*5*6).


ItemNo Multiplier
===== ======
3 2
3.1 5
3.1.4 6
3.1.4.7 4

Here, your count variable would be four, and your multiplier at the bottom
level would be 240 (2*5*6*4), so that you would need to order 240 pieces of
itemno 3.1.4.7 to make two assemblies of part 3. (If I understood your
process explanation correctly.)


ItemNo Multiplier
===== ======
3 2
3.1 5
3.1.5 8

Here you've gone back up one level and advanced to another branch, so your
count is three and your multiplier 80 (2*5*8).

As you read each record, you count the number of periods in the itemno to
determine the number of sublevels involved. You can't just measure the
length of the string, because you might have more than one digit describing
your sublevels. A simple way to count the periods is use the REPLACE
function and compare the length of the result with the length of the
original. Something like:

LEN(ITEMNO)-LEN(REPLACE(ITEMNO,".",""))

will tell you how many periods were replaced by nothing in the second part
of the expression, so you know how many sublevels deep you are. You would
also want to compare the parts of the string to determine how much of the
itemno has changed. INSTRREV will let you look for a period starting from
the right end of the string. Comparisons like:

IF LEFT$(ITEMNO,INSTRREV(ITEMNO,".")) <> ... THEN

can test if you've changed from 3.1.4.7 to 3.1.4.8 or gone on to 3.1.5 or
3.2 or even 4.

To count how many parts you need, a simple loop from one to the count
variable will scan through your multiplier array. Keep a running product in
the loop and when the loop ends, you know how many of that particular part
you need to order.

This doesn't cover all the details, of course, but it should be enough to
give a general idea of how to address the problem. Don't let my talk of
recursion scare you away. It truly isn't that difficult a problem once you
get away from SQL and bring the full VBA arsenal to bear on it.

Pete