I have a database of specimens for a natural history collection. The scientific names for the specimens have changed since the last name update ~10 years ago. The actual specimen entries are recored in a catalog that references unique IDs in Names to show the identification. I updated the names and IDs (which are formulated from the names) in Microsoft Access and I now have three spreadsheets: Names, OldNames, and Catalog.
[Names] structure AllID NewID AllID Name [OldNames] structure ID Name [Catalog] structure ID (could be an AllID or NewID) Specimen#
Now I need to find all the names that changed between AllID and NewID, locate the corresponding specimen by number, and physically change the label. My current code is to run a query with [Names].[AllID] linked to [OldNames].[ID] and [Catalog].[ID].
Field: AllID Table: Names Criteria: Not[Names].[NewID]
I also attached [Catalog].[Specimen#] so that I can locate the actual specimen.
The problem is that the query does not retrieve all of the changed fields. I noticed this because a physical specimen with an old ID was not on the list created by the query. Should line of code
Criteria: Not [names].[NewID]
return all IDs that were updated? If not, what other code should I use?
All help is appreciated, including a link to the same question asked by another user. This seems quite simple and basic but I'm unable to find an answer so far.