Add Join will not create a 1:M join

Question

I am having trouble doing a 1:M join in arcgis pro.  But the thing is, when I first tried it worked perfectly when I used the Add Join tool.

For my first try, I joined 182 points (SampleSites) to a related table containing the recurring sample data that is collected at each site (BacteriaSamples).  The resulting join has over 6,000 records with the warning (yellow triangle with exclamation mark) about "duplicate rows."  Exactly what I wanted.

When I try joining SampleSites to a different table (EcoliRatings_Quarters_Years), it only completes a 1:1 join.

According to the Add Join  reference page, "the input table must have an Object ID field to perform a one-to-many join.", https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm.

My data has an ObjectID field.

Not sure if this matters, but there is one difference between the two tables. The BacteriaSamples is an Enterprise GDB Table.  EcoliRatings_Quarters_Years is a File GDB Table. It was an excel sheet that I imported to my Pro project, using the Excel to Table tool.  The point file is also in the EGDB.

Somebody please advise, thanks!

Lynn

JayantaPoddar · Accepted Answer

Ensure that the Join table also has an Object ID Field.

Also try keeping both the point feature class as well as the table in the same geodatabase.

Working with a Relationship Class could be another option. Check it out as well.

danretired · Accepted Answer

make your table a geodatabase table... for instance if it is an excel or csv file, use the appropriate conversions to get them into a gdb.

And if they aren't in the same gdb.... copy one over so they are (TableToTable for instance