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