0

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.

  • Maybe you can show some sample data (or share small example database with sample data), and show desured result on that data with the explaination why each separate record was or was not selected? – Akina Apr 9 at 5:14
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.