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

Copper Contributor

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!

7 Replies

@LyonGIS Maybe create a relationship on two fields?

 

rbg.jpg

@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.

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.
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

@LyonGIS 

Unfortunately, that's not the most effective approach for relational database design, though. 
A genus can and does include multiple species. It's  form of category and sub-category. And that calls for two discreet fields in the context of a relational database.

 

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. 

 

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.

Of course you are right, but I am not asking about database design. I am asking how to concatenate values from two fields into another field. Forget about the database. Let's say I made a copy of the entire accessions table in order to export an extract as an XLS file for a statistician to use, and for that person's use the scientific name needs to be one field. How would I do the concatenation in Access?

 

Or let's say I need to do a join in ArcGIS which only supports joining on one field. Same thing, how to do the concatenation in Access? There are infinite other analysis scenarios where you need to concatenate or do math to generate the field you need and good database design shouldn't stand in the way of analysis.

 

I'm sure someone at some point decided you should never be able to set up a relationship with a calculated field, even though there are many reasons for such an analysis!

@LyonGIS 

That's a series of intriguing questions and insights.

 

Concatenation itself is pretty straight forward:

 

SELECT [Genus] & [Species] as CombinedScientificName

FROM YourTableNameGoesHere;

 

Or, perhaps:

SELECT [Genus] &  " "  & [Species] as CombinedScientificName

FROM YourTableNameGoesHere;

 

That's quite different, though, from defining a relationship between two tables. 

 

You can join on the concatenated ScientificName in that query if there is a field in another table or query which is identical in having the same two components. 

If the resulting ScientificNames are unique across the dataset, then you can turn that into a Make Table query, generate a new table with that combined field, and then designate it as a Primary Key in that new table. However, a lot of caution should precede such as design because it's a couple of steps away from the actual data.

 

I think, perhaps, one factor to consider is whether you are, in fact, defining primary and foreign keys in tables, or just using these concatenated fields for joins in queries. The latter is doable and reasonable common.