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