Matching a product field to another field that are not the same

[ChuckW]

Hi,

I have a table called Products that has two fields: ProductNumber and
ProductLine. There are about 2000 product numbers. The ProductNumber is
alphnumberic and can be 6,7,8 or 10 characters. The ProductLine field is a
alphanumberic characters.

I also have a transactional table called Sales2008 which gets appended daily
with new transactions. Two of the fields are PartNumber and ProductLine.
PartNumber is the same as ProductNumber except the our main transactional
system which is mainframe based and I cannot do anything about, adds 3-6
extra characters to the PartNumber. These extra numbers represent daily
transaction numbers. There are never two PartNumbers that are ever the same
(Even if they are for the same Product). The ProductLine field is blank
each day. Every morning someone who knows alot about our Product Lines
number, Part Numbers and ProductLines copies the daily transactions from
access to excel, looks through the Part Numbers and assigns them a
ProductLine. His process is entirely mental. He can spot the Product Number
inside of the Part Number and knows what Product Line it belongs to. This
takes 20 minutes a day and if he got hit by a bus, we would be in trouble.

What I want to do is to somehow cycle this Products table through the
Sales2008 table comparing the ProductNumber in the Products table to the
PartNumber in the Sales2008 table (even though they don't match) and then
populate the null values in the ProductLine field in the Sales2008 table with
values from the ProductLine field in the Products table. Can someone help me
with how to do this?

Thanks,

 

[Michel Walsh]

Make a new query, bring your table Products and a table with all possible
ProductNumber. I assume such table is called ProductNumbers. DO NOT JOIN the
two tables.

Bring Products.PartNumber, in the grid, add the criteria:

LIKE [ProductNumbers].ProductNumber & "*"


Bring ProductNumbers.Number in the grid.



It seems Products.ProductLine should be updated by
ProductsNumbers.ProductNumber, if I understand you well, and if the encoding
made by your main frame is without equivoque.



Hoping it may help,
Vanderghast, Access MVP

 

[ChuckW]

Michel,

Thanks for your help. I am having a problem with this though and am not
sure there is a solution. When I do you suggestion, it is creating some
dulplicate values. This is why. We commonly have two product numbers that
are very similar. One may be HA107A and the other may be HA107AC. There may
be a part number with the value of HA107AC0132. The method of matching that
you suggested matches this part number to both HA107A and HA107AC. Any
thoughts on what to do in this situation?

Thanks,


--
Chuck W

Make a new query, bring your table Products and a table with all possible
ProductNumber. I assume such table is called ProductNumbers. DO NOT JOIN the
two tables.

Bring Products.PartNumber, in the grid, add the criteria:

LIKE [ProductNumbers].ProductNumber & "*"


Bring ProductNumbers.Number in the grid.



It seems Products.ProductLine should be updated by
ProductsNumbers.ProductNumber, if I understand you well, and if the encoding
made by your main frame is without equivoque.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

There are many solutions, but none as 'immediate' as using the operator
LIKE.

1- Solve automatically the cases where there is just one match, and present
the cases where there is multiple possible matches to a human, so the human
may choose which match is the most appropriate.


2- Always use the longest match. With HA107A and HA107AC, the second one has
one letter more, so this would be the one to use. While it is easy to find
the longest match, we lose updateability, and the queries become hard to
code/maintain. You may have some success if you update with an ORDER BY
clause (but that may change from Microsoft service patch of Access to
Microsoft service patch of Access:

UPDATE ...
SET ...
ORDER BY partNumber ASC



Indeed, if partNumber ASC is respected in the sequence of SET, first the
update with HA107A will be done, but next, the update with HA107AC will
be involved and write over any previous update, as we want. (Note that
longer names come after shorter ones, when alphabetically ordered)
But again, Microsoft has not committed itself to perform the UPDATE
accordingly to the ORDER BY clause, so even if it works today, it may break
next patch/service update.






Hoping it may help,
Vanderghast, Access MVP

Michel,

Thanks for your help. I am having a problem with this though and am not
sure there is a solution. When I do you suggestion, it is creating some
dulplicate values. This is why. We commonly have two product numbers
that
are very similar. One may be HA107A and the other may be HA107AC. There
may
be a part number with the value of HA107AC0132. The method of matching
that
you suggested matches this part number to both HA107A and HA107AC. Any
thoughts on what to do in this situation?

Thanks,


--
Chuck W

Make a new query, bring your table Products and a table with all possible
ProductNumber. I assume such table is called ProductNumbers. DO NOT JOIN
the
two tables.

Bring Products.PartNumber, in the grid, add the criteria:

LIKE [ProductNumbers].ProductNumber & "*"


Bring ProductNumbers.Number in the grid.



It seems Products.ProductLine should be updated by
ProductsNumbers.ProductNumber, if I understand you well, and if the
encoding
made by your main frame is without equivoque.



Hoping it may help,
Vanderghast, Access MVP

Hi,

I have a table called Products that has two fields: ProductNumber and
ProductLine. There are about 2000 product numbers. The ProductNumber
is
alphnumberic and can be 6,7,8 or 10 characters. The ProductLine field
is
a
alphanumberic characters.

I also have a transactional table called Sales2008 which gets appended
daily
with new transactions. Two of the fields are PartNumber and
ProductLine.
PartNumber is the same as ProductNumber except the our main
transactional
system which is mainframe based and I cannot do anything about, adds
3-6
extra characters to the PartNumber. These extra numbers represent daily
transaction numbers. There are never two PartNumbers that are ever the
same
(Even if they are for the same Product). The ProductLine field is
blank
each day. Every morning someone who knows alot about our Product Lines
number, Part Numbers and ProductLines copies the daily transactions
from
access to excel, looks through the Part Numbers and assigns them a
ProductLine. His process is entirely mental. He can spot the Product
Number
inside of the Part Number and knows what Product Line it belongs to.
This
takes 20 minutes a day and if he got hit by a bus, we would be in
trouble.

What I want to do is to somehow cycle this Products table through the
Sales2008 table comparing the ProductNumber in the Products table to
the
PartNumber in the Sales2008 table (even though they don't match) and
then
populate the null values in the ProductLine field in the Sales2008
table
with
values from the ProductLine field in the Products table. Can someone
help
me
with how to do this?

Thanks,