arcgis desktop - One to Many / Many to Many

I have a sanitary sewer line feature class where each line has a unique identifier.

I have a table that lists the date a line was cleaned. It may have been cleaned multiple times so I can have multiple dates for each unique identifier.

I also have another table that lists the date a line was inspected. It may have been inspected multiple times so I also have multiple dates for each unique identifier.

I need to select each cleaned date and find an inspection date that lies within a 30 day range from when it was cleaned.

For Example,

From Table_Clean:

Line A is cleaned 1/1/2014
Line A is cleaned 4/1/2014
Line A is cleaned 8/1/2014

From Table_Inspected:

Line A is inspected 1/2/2014
Line A is inspected 4/2/2014

Line A is inspected 11/2/2014

I need to list that the first two were cleaned and inspected within 30 days. The third was cleaned but not inspected within 30 days.

Is this many to many relationship possible? And does anyone have any suggestions on how to symbolize this on a map? Obviously the same exact line feature may be represented twice being cleaned and inspected on different dates.

Answer

The kind of information relationship you're describing is normally handled with a Relate in ArcGIS. If your three tables are set up with a correct relate, you can click on a line and it will then show all matching records from the other two tables. But this is more a display/access of information rather than a use/analyze. In order to solve the specific problem you're addressing, I can think of one (untested) solution right off. Unfortunately it's manual, brute force in a way, and probably not very efficient.

First, you would need to run Make Query Table on your clean and inspect tables with the line ID as the key field. This addresses the many-to-many issue by creating a table record for all possible combinations of the two table's records. Obviously this generates a huge number of records - if a line has six cleans and four inspects, you get 24 records.

Once you have that table, you could do an attribute select with SQL to determine the target combinations - ie all records where clean.date and inspect.date are within 30 days of each other. I don't know the exact syntax off the top of my head, but it would be something like absolute value of datediff(d,clean.date,inspect.date) < 30. This could/would still produce multiple records for the same line - say if it was inspected 17 days before and 23 days after being cleaned. So from that selection, either by exporting to a new table or using another select from the selection, you'd then want to grab just the unique occurences of line ID.

Once you have that selection, you can again export it to a new table (pass) or if you really wanted to combine it all into a massive single SQL statement used to select records from your line table. Since that involves nested queries, everything has to be in a geodatabase for it to have a chance of working though. At any rate, you'd have your list of line IDs that have been cleaned and inspected within 30 days of each other, and you can then either get that as an attribute in the line table or use it to extract the needed lines to symbolize however you want. The whole process could be modified to also target lines cleaned but not inspected, inspected but not cleaned, or neither inspected or cleaned, so you could create a full map showing all four possible conditions.

This is the foundation I'd start with. It would probably have to be refined to a workable process and then turned into a script or model to be run on any kind of regular or repetative basis. Someone else may know of a more elegant solution and if it's in an SDE you may have other options as mapBaker alludes to.