Relate across multiple features/tables, highlight multiple objects in feature class

[Maps-Berlin]

Hey everyone,

Im trying to solve the following problem in ArcGis Pro and Im pretty certain that related tables may be the answer, but so far I‘ve had no luck in doing this on my own.

Basically I have data on borehole locations, samples taken from said boreholes and the geologic formations those samples represent. What Im trying to achieve is a table with unique entries of those geologic formations in where I select any formation, it will then highlight the boreholes on the map where samples of those formations exist. It would also be great if I could add a column to my formation list which contains all sample identifiers.

To better illustrate: 

I have 3 tables boreholes/samples/formations which are build like so:

from the Borehole table I created a feature class that shows all locations on the map, now I would love to be able to easily highlight where any formations were sampled by relating the formation table to borehole locations like so:

Any help to achieve this or maybe some idea forma workaround and which tools I should look up to do this would be greatly appreciated.

Thanks and have a wonderful weekend everybody.

[KimGarbade · Accepted solution]

If I am understanding your diagrams correctly it seems like multiple samples link to one borehole and that each sample already has its formation information associated with it. In this case I would load both the sample and borehole tables into ArcGIS (don't use the feature class you created, just the original tables) and, starting from the sample table, create a join to the borehole table using BoreholeName as the common key. Once the join is complete each sample should have a the original sample data (Sample name, formatio , etc) and  borehole name and the X,Y location of that borehole. Now you can use that X,Y location to create a new feature class of sample locations each point will have the data you want at the borehole location it was taken at (multiple points per borehole).  When you query the new feature class on Formation = whatever it will only return the points that have that formation. 

If you want just one point per borehole look into building a "relationship" between your borehole feature class and you sample points table. 

[Maps-Berlin]

Thanks a lot, I was somewhat hoping to get around creating a feature class for the samples because of the many redundant points this introduces. Anyhow, creating that feature class and then building a relation between it and the formation table accomplished what I was trying to do (beeing able to browse formations and clicking them to see corresponding samples on a map).