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.
Best Answer
I located the error: My joins were incorrect and the operation cannot be performed in Design View. I had to rewrite the joins in SQL view.
The original code:
Replace 'AND' with 'OR' to have the code function properly (although it can only be executed from SQL view)