Access query to locate changed records

Question

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.

Answer 1

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:

SELECT OldNames.ID, Names.NewID, Catalog.Specimen#, 
FROM Catalog INNER JOIN (OldNames INNER JOIN Names ON OldNames.ID = Names.AllID)
ON (Catalog.ID = Names.NewID) AND (Catalog.ID = Names.AllID)

Replace 'AND' with 'OR' to have the code function properly (although it can only be executed from SQL view)

SELECT OldNames.ID, Names.NewID, Catalog.Specimen#, 
FROM Catalog INNER JOIN (OldNames INNER JOIN Names ON OldNames.ID = Names.AllID) 
(Catalog.ID = Names.NewID) OR (Catalog.ID = Names.AllID)