Forum Discussion
How to use calculated field for a relationship (genus and species)?
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.
- 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
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!