Hi I have 2 tables in access one is a list of prices from my supplier with model numbers and the other is all my products with model numbers
I have made a query that matches the model numbers so I can update my database with my suppliers prices.
There is about 6000 products but the last 500 the model numbers do not exactly match some have - in them where mine do not and some have extra characters where mine do not
Hi I have 2 tables in access one is a list of prices from my supplier with model numbers and the other is all my products with model numbers
I have made a query that matches the model numbers so I can update my database with my suppliers prices.
There is about 6000 products but the last 500 the model numbers do not exactly match some have - in them where mine do not and some have extra characters where mine do not
I'm a beginner in Access, and today I was trying to learn how to create custom functions. I am wondering if I am missing something, because it seemed suprisingly easy - and quite powerful.
As you know there is a LIKE function, so perhaps your query is already doing something similar to this:
WHERE supplier.ModelNo LIKE tblProducts.ModelNo
of course this only finds exact matches. Why not define a function that assigns a "closeness-rating" to any two model numbers compared? Once the function is created, you can use it in queries I should think - use it like this (perhaps).
Code:
SELECT P.ModelNo as Product_No, S.ModelNo as Supplier_NO,
ClosenessRating(Supplier.ModelNo, Products.ModelNo) as Rating
INTO tblClosenessRatings
FROM tblSuppliers as S, tblProducts as P
And then you could use that table to pull the desired info. To create a function (hope I got this right) just create a new Module and add code such as this:
Function fullName(ByVal firstName as String, ByVal lastName As String)
fullName = firstName & " " & lastName
End Function
So you would ned a function called
Function ClosenessRating(ByVal SupplierNo as String, ByVal ProductNo As String)
'logic goes here
End Function
The ENORMOUSLY tricky part would be the logic of comparison. I am skeptical that it is logically coherent to even speak of a "closest match." Maybe I'll take a stab at it.
Nah, I'm not even going to consider trying this until I know what you're up against. I would need some sample data to get some idea of the comparison issues. Even then I am not sure I want to get further involved in this difficult problem.
And I am also curious why a "closest match" would be a solution to begin with - it doesn't seem very professional/ scientific for a business to base it's product line on model numbers which are only 'possibly correct' due to being 'closest matches'.
You have two possible routes, one labourious, the other technical
Labourious:
Run a mismatch query on both tables and manually revise the contents to suit the master and only allow items in the master file to to used.
Technical
Add a field to each table and insert a soundex value in this field based on the model number then when you do a lookup base the lookup on thesoundex value not the actual value.
