Nearest neighbor record?

[nycpost]

Hi,

I have a question. I have a database that tracks takes from a film shoot. I have two tables, one of takes from A camera and another from B camera. Both cameras were covering the same scene, but each started recording at slightly different times, so while they are in sync, their starting and ending timecodes are different. I need to form a relationship between the takes in these two tables. The most reliable value to form s relationship on is timecode. All the other values are text based and vary greatly in each table.

So, I need to find the "nearest neighbor" record (based on timecode) for A camera in the related B camera table. The records are never predictably earlier or later in TC, so forming a relationship to pull the next least record won't work. Sometimes the B camera started later, sometimes earlier. I've devised a solution where I created a calc field to determine the nearest neighbor which i then use in a relationship to pull the correct matching record. This seems to work fine, I'm just wondering if there's a better solution. Anyone have any ideas to find a nearest neighbor record?

Thanks,

Mike

[Matthew F]

It seems like you'd be better off with one table, and simply designate the camera name in a separate field. What happens if you want to add a camera? Will you need to add another table? If you had one table you'd just sort by Take# and then Timestamp.

[comment]

I too would suggest keeping all takes in the same table. To match up the takes, you can define a self-join relationship as:

Takes::StartTC ≤ Takes 2::EndTC

AND

Takes::EndTC ≥ Takes 2::StartTC

AND

Takes::TakeID ≠ Takes 2::TakeID

This will make any two overlapping (fully or partially) takes related to each other.

Note that at least in theory there can be more than one matching record - for example if one camera kept rolling while the other one stopped.

[nycpost]

Thanks, mfero and Comment. To be honest, I was trying to simplify the question for discussion purposes. The second camera is in fact not a camera at all. It's a metadata logging system that tracks information about the camera and lens. Since it's an independent system, it fires at slightly different times from the camera. So since it's really a different entity, I've kept it in another table.

Regardless, comment, your solution to link the two via a relationship where cam start tc <= data end tc seems to work great. it does in fact show you when a record in the metadata table refers to more than one camera take, but this is perfect. My calc field solution wasn't doing that, so that's a big help.

Thanks again and happy holidays!

Mike