Product Code Substitution Table

[Guest]

I have a table with sales history records. As usual we are constantly
changing product codes so I frequently need to consolidate the sales history
from an old item to a new item for forecasting purposes. I don't want to use
'find & replace' because if I refresh the sales history download my changes
will be lost.

I have a table which has three fields - OldItem; NewItem; Loc.

If I only had to deal with the Item, it would be easy with a join. However,
I am importing the table data from another application and some of the item
substitutions are global and some are location specific. Where the change is
global the Loc field is blank and if it is applicable to only 1 location, it
contains the location code.

I know I will probably be told that the data should be normalised and there
should be 1 record for every item/location record rather than just "blank" in
the Loc field for global changes, but I can't change that and I don't want to
have to turn 500 entries into 3000 manually.

Can I use a wildcard or IIf statement to solve this somehow ?

 

[Tom Ellison]

Dear Oz:

I don't see that what you have creates any normalization issue. The
challenge is not created by the way the data is stored.

As you say, the Item number changes from time to time. It would seem likely
that an Item might change from "A" to "B" and then from "B" to "C". If this
is the case, you have the need for recursion to repeatedly update the Item
number to what is the current value.

To do this effectively, I'm going to recommend breaking the rules. Getting
this recursively updated is a mess.

So, I'm going to recommend storing a derived value. To do this, you could:

- Add a column to the table of all this history: "CurrentItem". This is a
one-time change.

- Add a column to the table mapping the Item changes: "DateChanged". This
is essential because deriving the current value of an Item number is
dependent on making a sequence of changes in the proper order. For example:

Change A to B
Change B to C

This results in something that was A becoming C, and anything that starts
out being B becoming C as well. Reverse the order:

Change B to C
Change A to B

Now, whatever starts out as A ends up being B, and whatever starts out being
B ends up C. Not the same thing, for sure. Also:

Change A to B
Change B to C
(Now add some new rows that are A, a new and different item)

Here, the OLD "A"s become C, but the new "A"s remain A!

- Add a date column to the table of all history. Changes before this date
are not to be accounted.

Then, every time you need to know what is the proper CurrentItem value:

- Clear out (NULL) the "CurrentItem" value

- Get a list of all dates on which items were changed.

- (STEP X): Execute those changes for the oldest date ONLY to those items
created on or before that change date. First, update all those rows where
"CurrentItem" is NOT NULL. Then update all rows where "CurrentItem" IS
NULL.

- Move to the next "change date" (as listed two steps back).

- Repeat from STEP X till no new dates appear.

I hope I have put these pieces together correctly. I've had to do this
before myself. Without the dates on the creation of new items, and the
dates changes are effective, the result is not determinant. There would be
multiple possible results!

You can even run this to find out what would have been the item number on a
certain date in the past.

With MSDE I've accomplished this without the derived stored column, but I
wouldn't consider that doing it in Jet. This method limits the number of
recursions, as well, so it isn't as general.

Perhaps you situation is that no item number ever changes more than once.
Perhaps no discontinued item number is ever re-used. I haven't felt it
would be appropriate to presume this is the case.

Tom Ellison

 

[Guest]

Tom,

Thanks for taking the time to answer. I think that I may have asked the
wrong question.

I have a table that I have imported as follows (hope it makes as much sense
as when I pasted it) -

FPROD TLOC TPROD
F64651 SY F987897
F24161 F87966

In the above sales for F64651 will be revised to F987897 for Location SY
only but sales for F24161 will be revised to F87966 for all locations. My
problem was how to deal with the interpretation of the blank TLOC field to
mean "all".

I ended up creating a table with a record for each possible location (6 in
all) and then created a query with the following to join the 2 tables -

SELECT tblTransTest.FPROD, tblDC.LOC, tblTransTest.TPROD, tblTransTest.TLOC
FROM tblDC, tblTransTest
WHERE (((tblTransTest.TLOC) Is Null)) OR
(((tblTransTest.TLOC)=[tblDC].[LOC]));

Essentially it uses a Cartesian join to create a record for each Item/Loc
combination when the TLOC field is blank or carries across the existing
Item/Loc revision when there is a location specified in the TLOC field.

Not sure how elegant this is but I get the result I am after.

Thanks again for your help.

Regards,

IK