Good morning all,

I have been trying to figure this out all weekend.

Let me start off by saying that I by no means a programmer. I can fumble my way through most VBA using the web or bouncing ideas off others.

I have a situation now where I need to be able to historically change data without physically updating the existing records "important".

Right now, I have three cases

1. the new effective date is = to the existing effective date - ACTION: update the existing record with a replace timestamp; insert a new record with the new information with the same effective date

2. the new effective date is > the existing effective date - ACTION: insert a copy of the existing row with an end date one day less then the new effective date; update the existing row with a replace timestamp; insert a new row with the new information (effective date one day > than the end date.

3. This is where my issue is... the new effective date < the existing effective date. When there is only one record, this isn't an issue. I can just follow case 1 but when there is more then one record then there is a problem. I can not have 2 or more records "effective" in the same time period.

I need to find a solution that will enable me to search for existing records where the new record will be in between and then update each row in succession accordingly.

I hope that I have written out my issue properly.

Any help, pointers, links to other solutions would make my day!

Thanks for reading!

Stressed

I figured it out.

Steps:

1. Find record where the new effective date is between the old effective date and end date

2. Copy that row.

3. Logically delete that row

4. Change the end date to one day less than the new effective date

5. Logically delete all records where the new effective date is > the current effective date.

6. Insert the new record with the new effective date.

Complicated process but I end up with all the history that the user makes in real time.