Windows 10 
Access 2010 32bit
Hi all,
I am relatively new to using Access in my new job. I've been tasked with fixing what was though to be a simple issue with a database that contains a bunch of biological data (I'm an ecologist so it contains data from sampling freshwater streams). The previous employees that entered data into the database seem to not have taken the time to learn how Access works or how the database is organized, so I've had to do a lot of work (e.g., missing data, Unique ID's for sites that were never sampled, Typos in the unique ID's, etc etc). Currently, I'm addressing misspellings in the taxonomic names of organisms. To explain the organization a little: There is a table called "Benthics" that contains the unique ID for each sampling event (Field: ActivityID), an Identification for each taxa found in a sampling event (Field: FinalID), and the number of individuals of each taxon (Field: Individuals). The FinalID field is the key field that relates to a table that has a single entry for every Taxa and associated information about that organism (A master taxa list with life history information about those taxa; e.g., how it feeds, general behavior, how tolerant it is to pollution).
Until recently, my office performed identifications in-house, we now send our samples to a third-party taxonomy lab that sends up back an excel spreadsheet of the data. Prior to that recent change, data was entered from datasheets into the database with an Access Form. Over a few decades, there have been hundreds and hundreds of misspellings of taxa names in the Benthics table, and no one has gone through to check this. Our database is publicly available through a Web Search query on our website, and I noticed that if a taxon's name in the FinalID field is misspelled (and thus doesn't relate to an entry in the master taxa list), it does not show up on the Web Search query. Obviously, this is an issue for anyone trying to use our data.
To address the misspellings I have done the following:
I copied the FinalID field from the Benthics table into an Excel sheet and removed duplicates
I copied the related field from the master taxa list into the same Excel sheet.
I used a VLOOKUP-based formula to identify the entries in Benthics that are not found in the Master Taxa List (ideally there would be none; I found 430 unique misspellings; for example one family of insects had 6 different spellings)
Since then, I have been using the Find and Replace tool to replace each misspelling in the Benthics table.
However, this has been quite time consuming given the sheer number of misspellings (I know this database hasn't been properly QA/QC'd since at least 2005).
Is there a way to automate this process? For example, if I had an Excel sheet of each unique misspelling in column A and for each row in Column A had the correct spelling in Column B, could I have Access search for each occurrence of the values in Column A and replace them with the Value in Column B?
Reply With Quote
