How to use calculated field for a relationship (genus and species)?

Question

I'm a GIS and data manager at a botanical garden and we have an Access database that stores accessions going back over 100 years in a table with over 20,000 records. These are the plants that are brought into the garden. There are also tables with inventory data, geographic data, etc.

The main accession table has fields for genus and species, and I need to join a table I downloaded from the Royal Botanical Garden in Kew, UK. It has information about every species of vascular plant. But the only common information is genus and species. The accessions table has these stored as two separate fields and the Kew table has them both separate and combined.

I thought I could just create a calculated field and use that to create a relationship, but I discovered you can't use a calculated field for a relationship. I found several forum threads where folks are adamant that this is something you shouldn't do even if it were possible.

OK, so I should be able to create a stored value field and calculate genus + " " + species into that field. Right? Wrong! It doesn't seem to be possible in Access like it is in GIS systems. I found threads where people ask about calculating a value from two other values entered in a form, and storing that as a separate field, and the responses are basically that you shouldn't do that and should instead use a calculated field.

OK, so it's not possible or recommended to do either method. How can I do the join that I need to do? I can just put everything into a geodatabase and do the calculations in ArcGIS Pro, but it would be nice to do my database management in the database software!

xps35 · Answer

LyonGIS&nbsp;Maybe create a relationship on two fields?&nbsp;

lyongis · Answer

XPS35thank you, that worked! I had no idea you could have a relationship based on two fields.

BUT I've downloaded another table from the USFWS that has endangered species but with both genus and species in one field. So it would still be helpful to be able to combine both to one field to join with data that is structured that way.

george_hepworth · Answer

To the contrary. Putting two values in one field is bad design. Correct THAT table design flaw in a temporary table or query before importing its data into your table, which is more appropriately designed.

lyongis · Answer

It's two values but can be one value. Genus and species are commonly referred to as Scientific Name and presented as one string. In any case, I did separate it using ArcGIS Pro which lets you use Python to parse text very easily

george_hepworth · Answer

LyonGIS&nbsp;Unfortunately, that's not the most effective approach for relational database design, though.&nbsp;A genus can and does include multiple species. It's&nbsp; form of category and sub-category. And that calls for two discreet fields in the context of a relational database.&nbsp;While it is true that describing any given specimen can involve the scientific name stated as both genus and species, that alone doesn't rationalize ignoring the importance of good table design. Most cultures assign two or more names to individuals, commonly referred to as "first" and "last" names, plus other variations. Here again, the preferred status is to create two or more fields for those two or more names.&nbsp;&nbsp;In other words, the rules for good database design are based on how well that design supports the work required of the database. Your problem here is a strong case in point. When a source fails to observe good design principles--putting two pieces of data in a single field--complication and extra work are generated. Not that it can't be done with sufficient additional wads of compensating code. But good table design minimizes the need for that additional code wad.

Forum Discussion

How to use calculated field for a relationship (genus and species)?

Share

Resources