Ideas on how to accomplish this?

[GManNAtl]

I couldn't really think of a way to search for what I am trying to accomplish, so I apologize if this has been covered.

In one table I have two timestamp fields that are start time and end time. Occasionally, but not always one record is a "continuation" of a previous record. For example the end time of record 50 is the begin time of record 55 and they are basically a continuation of a single instance in separate records. There would also be a third field that will be an exact match between these two records.

Obviously, I could script a series of finds to accomplish this, but the table will be very large and I believe it will seriously affect performance as the table grows.

I don't expect anyone to completely write a function or calc to perform this, but if someone could point me in the right direction with a concept it would be appreciated. I haven't really had to do much with self-joins as of yet, but was curious if there would be someway to utilize a SJ to accomplish this? I believe that even an calc would even perform better than scripting a series of finds for each record.

Thanks in advance for any suggestions!

[comment]

Accomplish what? You have described the situation, but not your goal.

[GManNAtl]

I want to be able to match the two records and change the start time of the second record to match the first.

[comment]

Are you sure about this? This would be a permanent change - it would apply only to past records. It would also leave a large number of redundant records.

What exactly do you need to do next? I don't see why you cannot work with the data as is.

[GManNAtl]

Yes, I am absolutely sure! I am using the start time to lookup a related record in another table. I need both of these records to relate to the same record in the related table. Essentially both of these records SHOULD have the same starting timestamp. However, the device I am importing this data from does not provide the logs to me in this way.

If I explained the application it would make more sense, but to be perfectly honest I don't really want to disclose the details of what I am working on for the world to see.

[comment]

I am using the start time to lookup a related record in another table.

This can be done with no data modification. Define a self-join relationship matching on the common "third field", and a calculation field (result is Time) =

Min ( SelfJoin::StartTime )

and use this field as a matchfield for the lookup.

If you are intent on modifying your data permanently, then you can replace the contents of the StartTime field with the above calculation.

[GManNAtl]

Beautiful! I think this will get me where I need to go. By the way I don't necessarily want to manipulate the data. I think using your suggestion I'll create the new calc field as "modified_start_time" or the like and use this field for the relationship.

Either way, thanks for the help and direction.