I've got two vast tables of data which I need to link, however the field unique to each was, at it's source, a typed field, and as such both have errors, typos, formatting problems, known deviations etc.
An example would be something like this:
Table1: SFOC0912JB3
Table2: F0CO9I2JB3
(These are harware serial numbers for what it's worth). I could do with creating a link between the two tables which would return a true based on a number of possibilities, such as:
Match if:
- String matches with prepended 'S', 'C' and/or
- String matches with substituted 'I' and '1' in any or all positions and/or
- String matches with substituted 'O' and '0' and/or
etc.
I think Levenshtein had the right answer from what I've been reading, but I haven't yet found an implementation for access (freely) available.
For those who are interested in Approximate String Matching or those who could use these algorithms; I have a complete suite of Approximate String Matching algorithms written in Visual Basic in an Access database.
In 2004 I decided to jump into the world of Fuzzy Matching with both feet.
As it is, I am working for a company that deals with names, addresses, etc. very intensely. It is a fair sized company that
uses Access on a grand scale. Since I am an Access programmer, I work in an Access gold mine!
I knew that if I could get a good handle on Fuzzy Matching, that when I hit the right person at the right time, the company could greatly benefit from my research on Fuzzy Matching. The right time and the right person are not here yet.
Nevertheless, since I have reaped much free source code and information from the Web, it is now time to return the favor.
I developed a package that is sort of a demo/tutorial on Approximate String Matching algorithms in Access that is very robust in Fuzzy Matching. It would overtax the post in this forum for me to include it in a post.
To summarize, it works with the basic name - Last, First, and Middle. It has a user interface that allows a user to type in what would be a good name and what would be a questionable name to resemble the good name. The weighted results of all the various algorithms can be chosen, or an individual algorithm can be chosen to display how closely the names match.
In addition, it has a table of 17,295 known good names with unique ID numbers as a reference table, and table of 1200 morphed names that are typical of names entered in a database with no input conventions. These morphed names have typos, transpositions, variations on maiden names, etc. 1200 good names were selected for alteration and the unique ID of each original good name was stored in the table with the altered names to determine the accuracy of the matching process.
The morphed names were compared to the known good names in a query with an approximate join using the suite of algorithms to determine match percentage. The altered names, the ID number of the original good name, the ID number of the name it matched to, and the match percentage were stored in a results table to determine the results of the matching run.
These tables were used to test and tweak the algorithms by comparing the morphed names with the known good names. The results of 1322 names were saved to a results table with match scores.
The matching process was executed in a query with an approximate join using the suite of algorithms.
The match results:
Total Approximate Matches: 1188 (Recall) Precision Pct: 99.00%
Total Unmatched Names: 12 Unmatched Pct: 1.00%
Total Other Matches: 134 Other Matches Pct: .77%
The tables are accessible in the database, so anyone can run their own tests. The interface is set up to accommodate this as well.
The algorithms used: Dice coefficient as a threshold algorithm, Levenshtein Distance algorithm, Longest Common Subsequence, and the DoubleMetaphone. The names were passed to the algorithms by way of the bigram model.
I will email it to anyone who requests it.
It is in two platforms, Office 97 and Office 2000 as FuzzyMatching97.zip (692 KB) and FuzzyMatching2k.zip (721 KB). The zip files include ApprxStrMatchingEngine97.pps or ApprxStrMatchingEngine2k.pps respectively, StrMatching97.mde or StrMatching2k.mde respectively, IEEESoundexV5.pdf, and VBAlgorithms.txt.
IEEESoundexV5.pdf is an abstract about Approximate Sting Matching that fired my curiosity about the subject, and pertains to the package.
VBAlgorithms.txt contains the entire suite of algorithms in Visual Basic extracted from the MDB modules.
The PowerPoint presentations describe the workings of the MDE and give a good overview of Fuzzy Matching.
I have a form that has code tied to the 'on open' event that is going to be accessed by users where we want them to only have access to certain fields which we want them to fill out. The fileds that will be locked will change based on the field called 'Item Number'. The code will be long because there are 30 different Item Numbers and about 10 to 20 fields that we will disable based on the Item Number. The code is like:
Dim Item_Number As String If Me.Item_Number = "32000" Then Me.Batch_Lot_Number.Enabled = False End If
This is all great except that the disable makes the field kind of obscure by the color it gives it. I don't want to use the lock property because that doesn't give you a visual clue that its locked.
Is there a way to change the color of the field background using VBA?
Here is how you can use it - I provide this example:
Tables and queries can be created in the MDE database.
Create a table with known good reference strings. I created this one - REF_LIST.
It has one field, REF_STRING (Text) with a length of 50, and indexed (No Duplicates). The field length can be set to a length that suits your requirements.
This is the content:
REF_STRING Claw Hammer Cold Chisel Monkey Wrench Nail Gun
Create another table with strings to match. I created this one - TEST_LIST.
It has one field, TEST_STRING (Text) with a length of 50, and indexed (Either No Duplicates or Duplicates Ok depending on the data). The field length can be set to a length that suits your requirements.
Then create another table for the results. I created this one - RESULTS.
It has four fields, REF_STRING (same properties as in table REF_LIST), TEST_STRING (same properties as in table TEST_LIST), MATCH_VALU (Single, Fixed, 2 decimal places), and GOOD_MATCH (True/False).
This is the content from the results of the Match_Lists query.
INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU ) SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, IsSimilar([REF_STRING],[TEST_STRING]) AS Expr1 FROM REF_LIST, TEST_LIST WHERE (((IsSimilar([REF_STRING],[TEST_STRING]))>0.79));
Using this example you can populate the two tables, REF_LIST and TEST_LIST with strings that you need to compare and run the Match_Lists query.
The GOOD_MATCH field in the RESULTS table is for you or another human to determine if anything questionable is a good match for your purposes. If it is found that any match with a value of at least .95 is a good match then an update query could be created to update the GOOD_MATCH field with true for all those with a value of >= .95.
Then a select query could be created to look at those matches that do not have a GOOD_MATCH to determine if they may be good matches.
Naturally the two tables may need a unique ID for the strings for better tracking and comparing.
If so, create them and have them appended to the RESULTS table as well in the Match_Lists query.
I need to link two tables on the Name Field. The trouble is that the names are not enterred the same in each table, so I can't do a direct = comparison.
For instance, one table might have "The Heart Center of Indiana", while the other has "Heart Center of Indiana" Or one might have "St. John's Medical Center" and the other has "St Johns Medical Center" (or, god help me, "St John's Hospital")
My only thoughts are somehow building a matching rank by saying that 85% of the characters in "The Heart Center of Indiana" match "Heart Center of Indiana". There are thousands of names in each list, and I would very much not like to have to manually try to spot them.
I doubt there is a direct solution to my problem, so any tips on how I can make a translation table is aoppreciated.
For those who are interested in Approximate String Matching or those who could use these algorithms; I have a complete suite of Approximate String Matching algorithms written in Visual Basic in an Access database.
In 2004 I decided to jump into the world of Fuzzy Matching with both feet.
As it is, I am working for a company that deals with names, addresses, etc. very intensely. It is a fair sized company that uses Access on a grand scale. Since I am an Access programmer, I work in an Access gold mine!
I knew that if I could get a good handle on Fuzzy Matching, that when I hit the right person at the right time, the company could greatly benefit from my research on Fuzzy Matching. The right time and the right person are not here yet.
Nevertheless, since I have reaped much free source code and information from the Web, it is now time to return the favor.
I developed a package that is sort of a demo/tutorial on Approximate String Matching algorithms in Access that is very robust in Fuzzy Matching. It would overtax the post in this forum for me to include it in a post.
To summarize, it works with the basic name - Last, First, and Middle. It has a user interface that allows a user to type in what would be a good name and what would be a questionable name to resemble the good name. The weighted results of all the various algorithms can be chosen, or an individual algorithm can be chosen to display how closely the names match.
In addition, it has a table of 17,295 known good names with unique ID numbers as a reference table, and table of 1200 morphed names that are typical of names entered in a database with no input conventions. These morphed names have typos, transpositions, variations on maiden names, etc. 1200 good names were selected for alteration and the unique ID of each original good name was stored in the table with the altered names to determine the accuracy of the matching process.
The morphed names were compared to the known good names in a query with an approximate join using the suite of algorithms to determine match percentage. The altered names, the ID number of the original good name, the ID number of the name it matched to, and the match percentage were stored in a results table to determine the results of the matching run.
These tables were used to test and tweak the algorithms by comparing the morphed names with the known good names. The results of 1322 names were saved to a results table with match scores.
The matching process was executed in a query with an approximate join using the suite of algorithms.
The match results:
Total Approximate Matches: 1188 (Recall) Precision Pct: 99.00%
Total Unmatched Names: 12 Unmatched Pct: 1.00%
Total Other Matches: 134 Other Matches Pct: .77%
The tables are accessible in the database, so anyone can run their own tests. The interface is set up to accommodate this as well.
The algorithms used: Dice coefficient as a threshold algorithm, Levenshtein Distance algorithm, Longest Common Subsequence, and the DoubleMetaphone. The names were passed to the algorithms by way of the bigram model.
I will email it to anyone who requests it.
It is in two platforms, Office 97 and Office 2000 as FuzzyMatching97.zip (692 KB) and FuzzyMatching2k.zip (721 KB). The zip files include ApprxStrMatchingEngine97.pps or ApprxStrMatchingEngine2k.pps respectively, StrMatching97.mde or StrMatching2k.mde respectively, IEEESoundexV5.pdf, and VBAlgorithms.txt.
IEEESoundexV5.pdf is an abstract about Approximate Sting Matching that fired my curiosity about the subject, and pertains to the package.
VBAlgorithms.txt contains the entire suite of algorithms in Visual Basic extracted from the MDB modules.
The PowerPoint presentations describe the workings of the MDE and give a good overview of Fuzzy Matching.
My problem is rather complicated and I am not sure if Access is even capable of addressing it. As I said, this is a bit tricky and I understand if no one is willing to tackle it, however, I would really appreciate it if someone could tell me it is impossible if that is the case. Thanks in advance.
I have attached a table to better explain my dilemma.
I would like to use the information in the “Category”, “Range Start” and “Range Stop” fields to generate new identifiers for each record in the table. The simplest criteria would be to assign the same novel identifier to two records if they have the same values in all of “Category”, “R. Start”, “R. Stop” (This is the case for the first two records.).
I am able to use this approach but would much rather use a more sophisticated set of criteria. Specifically, to be assigned the same ID two(or more) records must:
A) Belong to the same category.
B) Their ranges must overlap by more than (x)(Where x is some amount of overlap). E.g. Records 3 and 4 should be assigned the same ID because their categories are the same and their ranges overlap by 333 (13222-12889).
C) Finally, if A is satisfied and B is not then the then records could be assigned the same identifier if the difference between their ranges is less than some value (y). E.g. Records 5 and 6 should be grouped because A) is satisfied and their ranges are only 5 apart -> (119-300)…(305-700)
There is no limit to the number of records that may be assigned the same identifier, provided they satisfy criteria A+B or A+C.
sorry if thi9s is the wrong place but I thought I would try somewhere.
I would like to create links to table trough an odbc link.
whta I have is an estimation programme that creates 4 tables in a directory/folder each time you create a new estimate I have an odbc link that points to the given folder where the tables are being created. What I would like to do is to search this folder and link specific tables to my database which is a contract control programme. the tabels are given unique name for each estimate example names are: 0001_assembly, 0001_data, 0001_increment, 0001_system
so to sum up is it possible to create a lnk to these tables on the fly. any one got any ideas and possible codes samples for doing this.
i have constructed a switchboard/main menu and have set up buttons which link to forms. i have also made a 'return to main menu' button which does as it says, the only problem is that after going back to the main menu both forms are now open...any ideas on how to stop this problem
I would like to set up a form in Access that accepts the input of an Excel spreadsheet file (browse for file) and then creates a pivot table on one of the sheets, and appends that data to an Access database table..
can you give me some ideas on how i should approach the problem?
This may seem like a silly question, but I can’t find the answer to it on this forum. People only say 'Remove the table links' but never how. I can’t seem to be able to remove the table links completely, so I only have one database file (no backend or frontend). I can’t seem to do it though the Table link manager or any other way. Any help?
I have an existing database using access 2002 which has been split. In the part containing the data (back end I think its called) there are 3 existing tables which are clearly linked from the front end (forms queries etc.). If I now try to add another table in the back end it is not shown in the list when trying to create a link from the front end.
I have recently learned how to link photos to a database using a form and a table listing the path to each photo. Is there anyway to format the records in the table so that when you click on the path the photo shows up?
I have browsed around past threads and have not found and answer.
I working on a Project in which I have a table that is being updated from the values in another table. The problem that the table is deleted and rebuilt in code before the Query is executed. Access is so smart, it drops the links between the tables when the table is deleted even if the query isn't open.
To get around this, I recreate the SQL in Code for the Named Query involved just before I execute it. This works OK but probably creates bloat and is a little slower.
Is there a better way around this problem other than recreating the SQL for the Update Query each time?
I have a query where I am trying to set the where criteria expression using an unlinked table. The unlinked table name is INV DATES. The criteria field is [THE_DATE]. I cannot really link the fields for the two tables since I am using an expression. This design works, but it goes really slow.
Any ideas?
SELECT DISTINCT DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER, DSSAPP_FULL_WIP_PROD_STATS.OPERATION, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY AS QUANTITY, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME AS [DATE], DSSAPP_FULL_WIP_PROD_STATS.LOT_HOLD_FLAG, DSSAPP_FULL_WIP_PROD_STATS.ACTIVE_FLAG, [INV DATES].THE_DATE, [INV DATES].WEEK_NUMBER_IN_YEAR, [INV DATES].MONTH_NUMBER_IN_YEAR INTO ROXES FROM DSSAPP_FULL_WIP_PROD_STATS, [INV DATES] WHERE (((DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R4*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "RR" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B0*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B4*") AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME)<=[INV DATES]![THE_DATE]) AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME)>[INV DATES]![THE_DATE] Or (DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME) Is Null) AND ((DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME )>=Now()-600) AND ((DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="STPPRD" Or (DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="EPIPRD")) ORDER BY DSSAPP_FULL_WIP_PROD_STATS.OPERATION;
Dear all, imagine a table named "Database" with fields
Par1, Par2, ... ParN, Amount,
where most of the parameters in Par1,...ParN are in text format. The structure of another table, "Criteria", is exactly like "Database" less "Amount" field. Suppose we want to summarise "Amounts" from "Database" according to the sets of "Criteria", for each of its records. A set does not always include all of the fields, e.g. an empty field (or sort of wildcard) may mean that any value fits. It seems like classical application of DSum function, but I've already spent a lot of time in desperate attempts to solve this problem and will highly appreciate your suggestions.
I wonder if you can help. I am trying to make a combo box with peoples ID numbers in which links to a table with that persons details. I have made the combo box with the names in but now trying to link the table to it. For example: Fred is in the combo box with ID number 1 and the user selects him and wants to see his details about him after clicking continue. The form has autonumber 1 showing Fred's details after clcking this. Any help Cheers John :cool:
As i'm still what can be called a "newbie" in MS Access, maybe one of you wizards can help me out with this.
I'm having a product table in ms access, containing a few columns. The regular stuff (type, ID, ..). It also contains a column i want to store the links to their documentation to, called "download'.
The fields of that table are loaded into a data access page.
Now the data access page is loaded into the frame of an HTML page. Everything works well, but when it comes to the download-field, i get no link, but a piece of code instead.
Now these links are relative links (not absolute ones). What i'm searching for (for quiete some time now), is a way to make the links show as normal links.
Is there anyone who could give me a hint or who can help me out with this?
We've got an old database consisting of just an MDE file and its point to the UNC path of a server we need to decommission. Is there anyway I can amend the Links to an external mdb so it points to either the new server or folder?
I have a splitted database. I have moved the location of these files to a new directory and when trying to run the refresh links, I get a warning for each table. Is there a way I can avoid this? I run the Linked Table manager utility but get always the window "select new location for table....". Considering that my program has a lot of tables, is there a way I can get around this problem? Thank you.
I was recently doing a little Spring Cleaning in my database and deleting old tables and queries that were no longer used or had been for experimental purposes. Unfortunately, I unknowingly deleted a query that was being used by a report I still needed and it took me several hours to find and correct the problem.
Not wanting to repeat this mistake, I began searching for a way to display my reports, queries, and tables and all the objects each is linked to. I thought it would be nice, for instance, to have a hard copy list of all my queries that showed where they are derived from and what other objects reference them. Is there a way to do this?
I've tried using the "Relationships" button and working with the Documenter, but I haven't found what I'm looking for. I have also tried a Google search, but, admittedly, I may not be using the proper terminology to get good results.
I am starting to add a list of useful Microsoft Access sites and forums to help beginners to Microsoft Access find what they need. If you have any useful links, then either post them below or send them to me via PM.
I thought this would be a good place to ask as many people here know quite a few good sites.
I am using 97 and have been running into problems with tutorials from microsoft since they are in 2000 format. Its not a big difference so maybe I just cant figure it out...
Anyways, I wanted to make a list of the reports I had for the user to click on one and have it pop up. Anyone know how I can make this list?
My idea is to somehow create the names in the list box using a query or something and have them be links to the report somehow...
myDatabase has a load of linked tables from myTables. Problem; my network drives are mapped differently from the users, so the links do not work for me.
So - does anyone know how I can set up a relative link, so myDatabase looks for datamyTables? i.e. it looks from the source directory rather than checking the whole path back to the root?
