Design strategy

[Jürgen Germonpré]

Dear All,

This will be a bit difficult ot explain, hope it clear.

As a product manager I'm responsable for a few brands. This means, I need to
keep SAP up-to-date for these products. t.i. buying and selling prices,
taxes etc.... On the other hand i retrieve data from SAP for analyses and
reporting. I'm only autorised to retrieve data from SAP. The data for update
is transmitted to our administrator who does the actual update.

This is what i have to do:
- Each month i receive a pricelist from the supplier. This is excel workbook
which i import into Access.
Same thing for the pricelist my colleagues in Holland. Some products,
although almost physicaly identical (they have the same EANcode), do have a
different ItemNo (last two digits do change), because of some differences
(f.i. french/english keyboard). For all articles that match, i check prices,
taxes and etc... Sometimes it's cheaper to buy in Holland.... that's
easy.

- Import the article range from SAP, and match with supplier pricelist. This
is where i look for differences in pricing, taxes and ect. versus our live
data !
Here i have some issues...:
(1) is that article references are not consistent. Strange but true, some
ItemNo's changed overtime, although it remained the same product !!! (last
two digits) So at a certain time in history they've decided to chop off the
last -ever changing- digits. Even by chopping off, articles still have a
unique identifier with this.
This means, that some times i have article with ItemNo like 4587D5478 and
sometimes like 4587D5478AB which becomes our internal SAP reference (style:
CAN-4587D5478 ).

(2) Each record has a SupplierCode to keep the link with the original
reference which contents should be the same as the ItemNo. But a result of
the inconsitency of input, sometimes it isn't !!! Sometime they do match on
the whole, sometimes on the ItemNo without the last 2 digits.... Some times,
this reference does not have anything to do with it ! So i have to look it
up manually, verify eancodes, descriptions and etc... to detect if there is
a match....
Pretty time consuming.

Now, i thought it would be simple to make a mapping table, where i do the
work once: map SAP ID onto ItemNo and then afterwards i use this table to
join SAP list and Supplier list together. I created a new table, using a
right join with ALL SAP ID's and the ItemNo where i have a match, the
others are empty, afterward, manually i can add missing or malformed ItemID.
SAP ID is primary, so I can't have duplicates on that one. I can't put an
constraint on the ItemNo, since initially i have a lot Null's in ItemNo....
Then i try to match as much as possible by joining SAP ID - SupplierCode,
with or without the last digits.....(about 4 queries, each time join between
another field).
First Problem is, if i join tables by means of this intermediate table (no
referential integrity), i can not edit the fields in the query. So i have to
open up other queries check visualy, copy paste references and etc.....
Next month i take the new SAP list and APPEND it to the mapping table, this
way i just add new articles (matches or not), while old entries are not
affected, since they could have already a match in the ItemNo field.

This may seem a bit ackward, but i don't know a better solution...
So I wondered, wouldn't it be better to alter one of the tables, add an
extra field for matching with the other table?? But then again, since each
month both tables are replaced i have to reconnect articles.... (i could use
the mapping table for this).
Or do i have to use a double primary key SAPID and ItemNo. or or or....

I just can't figure out wich way to go to make it simple an efficient.

Thanks for your advice.

JG

 

[Fred]

I noticed that nobody answered. If I may offer a few suggestions.

I read your entire post twice and couldn't figure out what your specific
question is, or what you are specifically trying to do. Of course there is
a lot of material in your post, but no such statements.

Sincerely,


Fred

 

[Microsoft]

I was afraid so...

In fact i have two tables let's say SAP and BRAND.
I have to join records from both tables in order to check on changes (record
deletions, additions and updates)

(1) Some records can be joined on the key in SAP and the key in BRAND.
Others have to be matched manually by means of other information in the
tables (f.i. product description, ean-code, specs, etc...) because the
creation of the keys in SAP has not been consistent (so SAPkey could be
slightly different from BRANDkey).

(2) SAP and BRAND are 'refreshed' independantly. That is, it's possible to
have records in SAP with no match in BRAND and also records in BRAND with no
match in SAP.
So i want to avoid the 'manual match' each time i refresh one or both of the
tables.

I thought creating an intermediate table MAPPED containing both keys would
solve the problem, but it doesn't quite.
Because of referential integrity i have difficulties (can't add a records'
SAPkey without matiching BRANDkey, or delete a records' BRANDkey) managing
the following two situations:

In fact,
BRAND can have records that stop to exist because of 'end-of-life' of the
product, but its counterpart in SAP must continue to exist because we still
have to sell out our stock, and in the end it must continue to appear in the
query 'Pricelist' based on this join.
on the other hand
SAP can have records that are not in BRAND because there were not created in
SAP (based on some criteria, there were not uploaded into SAP because of
lack of interest in the type (f.i. calculators) of products and because we
hold no stock of them, until, maybe someone does want to order the product).
They also have to appear on the pricelist.

Tables:

SAP
SAPkey (PK)
attribute1
attribute2
....

BRAND
BRAND (PK)
attribute1
attribute2
....

MAPPING
SAPkey (PK)
BRANDkey (PK)
attribute1
attribute2
....

Hope this is clear.

JG

 

[Larry Daugherty]

It sounds like you already have a mess and that you are trying to make
a somewhat orderly process out of total chaos. Is that about right?

Please start over yet one more time: Describe your Problem space
completely and independent from your desired solution in one or more
paragraph(s). Next, describe your Desired Solution separate
paragraphs. These descriptions from you should be in Real World
terms, not Access or database speak. These are the most important
things for you to understand as well as being the best way to
communicate to us.

HTH

 

[Jamie Collins]

i have some issues...:
(1) is that article references are not consistent. Strange but true, some
ItemNo's changed overtime, although it remained the same product !!! (last
two digits) So at a certain time in history they've decided to chop off the
last -ever changing- digits. Even by chopping off, articles still have a
unique identifier with this.
This means, that some times i have article with ItemNo like 4587D5478 and
sometimes like 4587D5478AB which becomes our internal SAP reference (style:
CAN-4587D5478 ).

CREATE TABLE Test (itemNumber VARCHAR(13) NOT NULL)
;
INSERT INTO Test (itemNumber) VALUES ('4587D5478')
;
INSERT INTO Test (itemNumber) VALUES ('CAN-4587D5478')
;
INSERT INTO Test (itemNumber) VALUES ('4587D5478AB')
;
SELECT T1.itemNumber, T2.itemNumber
FROM Test AS T1
INNER JOIN Test AS T2
ON MID(T1.itemNumber, INSTR(1, T1.itemNumber, '-') + 1, 9)
= MID(T2.itemNumber, INSTR(1, T2.itemNumber, '-') + 1, 9)
;

 

[JG]

It sounds like you already have a mess and that you are trying to make
a somewhat orderly process out of total chaos. Is that about right?

That is correct.

Please start over yet one more time: Describe your Problem space
completely and independent from your desired solution in one or more
paragraph(s).

I'll try once more,

There's a SAP on one side containing all information about the products we
sell, on the other side i recieve a new pricelist on a regular basis. I need
info from both SAP and pricelist, to verify for price changes, product which
are eol and etc... and eventually adapt SAP to the new situation. Not all
the information in the supplier pricelist is in SAP but i still need it for
other purposes.That's why i need to hook up both SAP and pricelist.

I cannot update SAP directly, so i have to download a list of the active
products for use in my personal DB, and in the other direction, i have to
generate an list (file) that reflect the changes our datatabase admin uses
file to update SAP.

About the data:
Products which disappear in the suppliers pricelist are eol products, but
they continue to exist in SAP as long there is some stock left in our
offices.

Also data could be changed in SAP manually (f.i. special price drops on some
products, temporary bundled products) done by other colleages. So each time
i download the SAP selection there is a possibility that data is not the
same with the download i did earlier.

There's no consistent link one-on-one between SAP and the pricelist. I can't
match records on both sides without manually verifing the unmatched records.

Next, describe your Desired Solution separate
paragraphs. These descriptions from you should be in Real World
terms, not Access or database speak.

I need a solution to avoid the manual matching of records between SAP and
pricelist, each time there's a new issue of the pricelist or a status change
in SAP.
It should be as simple as: importing the supplier pricelist and the current
selection of active products in SAP, into my DB.

And then i can...
- check if there are new products in supplier pricelist that are not in SAP
=> generate a list for our DB admin to create new entries for these
products.

- check if there are discontinued (disappeared) products in the supplier
pricelist => generate a list for DB admin to mark these products with a
certain code

- check if there are price changeds in the supplier pricelist that need to
be reflected in SAP => generate a list for DB admin to do the updates

After the DB admin has done the updates i re-import the SAP information to
check if all requested changes have been made and to match the newly added
records in SAP with the supplier pricelist

From here on i generate reports:
- pricelist for the sales dept.: all the products out of SAP + some info out
of the supplier pricelist
- reporting on sales, stock, etc...
- forecasting of sales


I'm not used to describe these kind of situations, so i hope i made it clear
now...

Thanks.

 

[Jamie Collins]

There's no consistent link one-on-one between SAP and the pricelist. I can't
match records on both sides without manually verifing the unmatched records.

Demo code using Jet data types and their synonyms as an example
(ANSI-92 Query Mode SQL syntax):

CREATE TABLE JetDataTypeSynonyms
(
data_type_name VARCHAR(50) NOT NULL,
data_type_synonym VARCHAR(50) NOT NULL,
UNIQUE (data_type_name, data_type_synonym)
)
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('FLOAT', 'FLOAT')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('FLOAT', 'DOUBLE')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('FLOAT', 'FLOAT8')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('FLOAT', 'IEEEDOUBLE')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('FLOAT', 'NUMBER')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('REAL', 'REAL')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('REAL', 'SINGLE')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('REAL', 'FLOAT4')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('REAL', 'IEEESINGLE')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('DECIMAL', 'DECIMAL')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('DECIMAL', 'DEC')
;
INSERT INTO JetDataTypeSynonyms (data_type_name, data_type_synonym)
VALUES ('DECIMAL', 'NUMERIC')
;
CREATE TABLE Test (text_col VARCHAR(50))
;
INSERT INTO Test (text_col) VALUES ('single')
;
INSERT INTO Test (text_col) VALUES ('IEEESINGLE')
;
INSERT INTO Test (text_col) VALUES ('DOUBLE')
;
INSERT INTO Test (text_col) VALUES ('YIPEEEDOUBLE')
;
INSERT INTO Test (text_col) VALUES ('TRIPLE')
;
INSERT INTO Test (text_col) VALUES ('float8')
;
INSERT INTO Test (text_col) VALUES ('float9')
;
INSERT INTO Test (text_col) VALUES ('NUM')
;
INSERT INTO Test (text_col) VALUES ('NUMBER')
;
INSERT INTO Test (text_col) VALUES ('NUMERAL')
;
INSERT INTO Test (text_col) VALUES ('NUMERIC')
;
INSERT INTO Test (text_col) VALUES ('decimal')
;
INSERT INTO Test (text_col) VALUES ('decimate')
;

SELECT DT1.text_col, DT2.text_col,
DT2.data_type_name
FROM (
SELECT T1.text_col, S1.data_type_name
FROM Test AS T1
LEFT JOIN JetDataTypeSynonyms AS S1
ON T1.text_col = S1.data_type_synonym
) AS DT1
LEFT JOIN
(
SELECT T2.text_col, S2.data_type_name
FROM Test AS T2
LEFT JOIN JetDataTypeSynonyms AS S2
ON T2.text_col = S2.data_type_synonym
) AS DT2
ON DT1.data_type_name = DT2.data_type_name
ORDER BY ISNULL(DT1.data_type_name) DESC,
DT2.data_type_name
;

Jamie.

--