Add Join will not create a 1:M join

[LynnBerni]

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 solution]

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.

[LynnBerni]

Thanks, for the scenario that didn't work, the join table does have Object ID....but I think the problem must be that the data is NOT in the same gdb.   We do have relationship classes, but I need to join the related data because I want to symbolize the points based on the data. 

[DanPatterson · Accepted solution]

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

[LynnBerni]

thanks, it is a geodatabase table.  I just exported the points to the file gdb, and now it works.  So the problem was definitely the data being in two different gdbs!