REALLY complicated (I think) replacements list for different part numbers issue

[CompleteNewb]

Okay, I've got a pretty complex issue here, and was hoping someone had the
time to help or advise me. It's posisble that I might be trying to solve
the problem in the wrong way, as well as not knowing quite how to solve it
using my own ideas.

We ship car part orders, but often have the ability to replace items on an
order with other items based on certain criteria. For instance, maybe the
orderd part is out of stock, but the replacement part is the exact same
color and size, or might be different color but is the same price, etc.

So for every part we have a list of possible replacements, ranked in order
(some replacements are better than others), and the reason they are a
possible replacement. For instance:

Original Part Replacement1PartNum
Replacement1Rank Replacement1Reason
OE54 TF32
1 SameSpecs
OE54 OM23 2
SameColor
OE54 OE16
3 SamePrice

So above, you see that any order that contains part # OE54, if we are out of
that part, we could potentially replace it with parts TF32, OM23, or OE16.
We'd use TF32 first, unless we were out of it, and in that case we'd use
OM23, etc. (the Rank is the preference for replacing with that part).

Now here's another complication. TF32 might be a good replacement for OE54
because it's the same specs, but it might also be a replacement for a
different part number for a different reason. For instance, TF32 might be
the same color as XJ33, but not the same specs. So for a more complete list
of replacement parts (instead of just the replacements for ONE specific
part), we have this:

Original Part Replacement1PartNum
Replacement1Rank Replacement1Reason
OE54 TF32
1 SameSpecs
OE54 OM23 2
SameColor
OE54 OE16
3 SamePrice
XJ33 YU25
1 SameSpecs
XJ33 TF32
2 SameColor
XJ33 KN54
3 SamePrice
TF32 OE54
1 SameSpecs
TF32 XJ33
2 SameColor

So you see the obvious trend that if one part is a replacement for another
part for some reason, it makes sense that they are interchangeable for the
same reason. BUT THIS IS NOT ALWAYS THE CASE. Just so you don't assume
it's a rule. As you can also see, though, different parts are possible
replacements for other parts for completley different reasons. Basically,
the important thing is, we have a list of parts, then a ranked list of
possible replacements, and WHY they are possible replacements. Now here's
why:

We have a different list for the packing person so they have it to refer to
as they box up the order; they know that if OE54 is out, their first choice
is to replace it with TF32, etc. The current list is in Excel, with every
replacement listed in one cell to the right of the original Part Number. So
it looks likes this:

Original Part Replacements
OE54 TF32 (SameSpecs), OM23 (SameColor), OE16
(SamePrice)
XJ33 YU25 (SameSpecs), TF32 (SameColor), KN54
(SamePrice)

So the packager, when he receives an order that has OE54 in it, can look at
this list and see that if the customer wants the same specs if we're out, he
should use TF32. However, if the customer has specified that any
replacement must be the same color and the specs aren't all that important,
then the packager knows he should use OM23 (it's the same color, even though
it's ranked as a 2 instead of a 1). So when the customer has not specified
ANY replacement criteria, the packager will always use what's listed first.
If the customer specifies something (like that the color must match, in my
example), the packager will look at this replacements list and choose OM23
instead, since it's the same color. THE RANK IS NOT ALWAYS BASED ON THE
SAME THING. It's not always the case that SameSpecs will always have a rank
of 1. Sometimes things are ranked as the #1 replacement because it's the
same color, not the same specs, etc. Plus, some of the part numbers have a
lot more than just 3 replacements.

Now, when we run out of something, we want to remove the instance of that
part number from the replacements list, so the packager doesn't have to keep
looking through the replacements list and realizing that we're also out of
replacements. For instance, when we run out of TF32, we don't want TF32
listed as a possible replacement for anything else (since we're out; you
can't replace anything with TF32, because we're ALSO out of TF32).

So what I'm TRYING to do is somehow automate the removal of out-of-stock
items from the possible replacements list. Right now (and don't laugh,
here, please), when we run out of TF32, I have to MANUALLY go through and
search for TF32 in the replacements Excel list the Packager has, and delete
the TF32 (as well as its reason for being a replacement) from the list. So
let's say we have this list starting out:

Original Part Replacements
OE54 TF32 (SameSpecs), OM23 (SameColor), OE16
(SamePrice)
XJ33 YU25 (SameSpecs), TF32 (SameColor), KN54
(SamePrice)

Then we run out of TF32. So we don't want TF32 listed as a possible
replacement anymore. So the new list needs to be like this:

Original Part Replacements
OE54 OM23 (SameColor), OE16 (SamePrice)
XJ33 YU25 (SameSpecs), KN54 (SamePrice)

So can someone help me with the possibilities of doing this? I have tried
using a combination of Access queries that are used to export the new list
for the packagers, but so far getting the replacements to list out, in order
of rank, including the replacement reason, with commas, etc. separating them
in Excel has eluded me. I'm not TOTALLY married to Excel, because if we can
save the hours it takes to manually generate the new list (going through and
deleting every instance of the newly-out-of-stock item in the replacements
list BY HAND) by using something different, I think it's worth it to change
formats. But if it's possible to have the final output in Excel, that's
what everyone currently uses, and you know how much people hate change.

I apologize for the length and complexity of this, but I'm in over my head
here. Any advice, diferent viewpoints, etc. would be very welcome, and I
very much appreciate your time. If someone can help me get through to the
end of this, I am totally happy to buy you lunch at your favorite restaurant
(well, not if Ruth's Chris is your favorite restaurant. But I mean, c'mon,
think about your health here; steaks in sizzling BUTTER?!! You'll be dead
before you get to dessert!!).

Thanks for any help, and thanks also for reading.

PS: Sorry about the Excel/Access cross-post, but I'm a user/experimenter
with both, and thought someone with either viewpoint might have useful
ideas).

The Complete Newb

 

[NetworkTrade]

that's quite a tome you've written.....

you got to have a straight forward PartsList table that lists all parts AND
their inventory quantity

you need a replacement table much like you explained it

straight forward to make a query of replacement table based on criteria of
primary part....will return records of all replacement candidates...

I wouldn't NOT show an item if quantity is 0 - although that is very do-able
but you remove intelligence, I would show all replacement candidate with
their ranking even if with 0 quantity in inventory.......

 

[James A. Fortune]

Okay, I've got a pretty complex issue here, and was hoping someone had the
time to help or advise me. It's posisble that I might be trying to solve
the problem in the wrong way, as well as not knowing quite how to solve it
using my own ideas.

We ship car part orders, but often have the ability to replace items on an
order with other items based on certain criteria. For instance, maybe the
orderd part is out of stock, but the replacement part is the exact same
color and size, or might be different color but is the same price, etc.

So for every part we have a list of possible replacements, ranked in order
(some replacements are better than others), and the reason they are a
possible replacement. For instance:

Original Part Replacement1PartNum
Replacement1Rank Replacement1Reason
OE54 TF32
1 SameSpecs
OE54 OM23 2
SameColor
OE54 OE16
3 SamePrice


I apologize for the length and complexity of this, but I'm in over my head
here. Any advice, diferent viewpoints, etc. would be very welcome, and I
very much appreciate your time. If someone can help me get through to the
end of this, I am totally happy to buy you lunch at your favorite restaurant
(well, not if Ruth's Chris is your favorite restaurant. But I mean, c'mon,
think about your health here; steaks in sizzling BUTTER?!! You'll be dead
before you get to dessert!!).

Thanks for any help, and thanks also for reading.

PS: Sorry about the Excel/Access cross-post, but I'm a user/experimenter
with both, and thought someone with either viewpoint might have useful
ideas).

IIRC, in about 1997, using A97, I wrote a similar database for a
salesman named Steve Dixon at Camcar Textron in Michigan. Camcar
Textron manufactures lots of different kinds of bolts for the auto
industry. The database would find similar substitution bolts based on
diameter, thread, material, etc. and possibly ranked the results. I
will look to see if I still have the database in order to give you some
hints. The best hint I can give is to make sure that design engineers
approve the substitution. Not too long after that database was in
place, Camcar Textron got into a lawsuit over using substitute bolts
where dissimilar materials caused a dielectric effect which caused the
bolt or the material around it to rust prematurely. I never found out
if the part substitution database was involved in that or not.

BTW, being the unorthodox kind of person that I am, I usually eat
dessert first. It gives me the quick energy I need to eat that steak .

James A. Fortune
(e-mail address removed)

Me at the 16th hole at Katke-Cousins Golf Course, Oakland U., c. 1997:

Steve, we're down. I've never lost a bet in golf in my life. Do what
you have to do to put us in a position to win.

Steve: O.K., I'll push.

Later at the 18th hole:

Me: I can't believe he drove the green off the tee on a par 4. We'll be
a stroke down on the green.

Steve: All I have to do is make this 26 foot putt for a birdie and hope
he misses the six footer he left after his first putt. Plunk.

 

[Paul Shapiro]

You could think about a Part table and a PartSubstitution table. The Part
table would have partNumber as it's primary key. The PartSubstitution table
would have (originalPartNumber, substitutePreferenceRank) as it's primary
key and substitutePartNumber and replacementDescription as non-key
attributes. substitutePreferenceRank is the preference level (1 being the
best substitute, then 2, etc.). If you could have ties for the substitute
order level, so it's not unique, you could use (originalPartNumber,
substitutePartNumber) as the primary key and then you could allow duplicate
substitutePreferenceRank values for the same orginalPartNumber.

 

[James A. Fortune]

You could think about a Part table and a PartSubstitution table. The
Part table would have partNumber as it's primary key. The
PartSubstitution table would have (originalPartNumber,
substitutePreferenceRank) as it's primary key and substitutePartNumber
and replacementDescription as non-key attributes.
substitutePreferenceRank is the preference level (1 being the best
substitute, then 2, etc.). If you could have ties for the substitute
order level, so it's not unique, you could use (originalPartNumber,
substitutePartNumber) as the primary key and then you could allow
duplicate substitutePreferenceRank values for the same orginalPartNumber.

That's a good idea, but it only works well if CompleteNewb follows my
advice about getting substitute parts approved . If any nearby
substitutes are allowed then a substitute parts table just gets in the
way. An order for replacement preference is a nice idea since it forces
someone to look at the choices and rank them. Perhaps one form can be
used to get all of the part's neighbors and another can be used for an
executive to assign substitution rankings for the possible neighbors and
insert them into the substitution parts table.

James A. Fortune
(e-mail address removed)