Importing records and change effective date range columns if detected on appends

[vetabz]

Hi All,

(after typing I realized this is very long, but my problem is really very simple it requires an intermediate skill to perform and I'm novice. This should be an exciting one for you. I would really appreciate your help on this one, this would definitely take away some of your time and patience - so thank you in advance! looking forward...)



Here's the scenario

I have a brand new shiny database, and I hope all these steps can be performed via SQL query alone

1. I wanted to import this excel file into a table(a new one), let's probably name that new table as "BASE"

2. Let assume today is January 1, 2017.

• Via SQL query, I would like to add 2 fields into that newly created table
• field 1 would be named "Start Date" and field 2 as "End Date".
• For all the records that was imported I would like to assign "Start Date" as the assumed date January 1, 2017
• for the "End Date" let's just set it to 1/1/2030

***I highlighted record id #2 for a reason, continue reading please.

3. Next, let's assume bullets #1 & 2# is already in place.

On another time (let's just say January 5, 2017) I imported another excel file (screenshot)
Note that in this new file only item #4 or record id #2 has changed since the first import, "Cat" is now categorized under "D"
I only want to append that particular entry to the "BASE" table, remember we named our initial table as "BASE" in bullet #1.
***my idea here is to create a temporary table say (and to make it obvious, "APPEND_TO_BASE") and maybe something like
a join... a LEFT JOIN perhaps to grab the unique item only that will be appended into the "BASE" table.

4. Now that the "Cat" with her new Category "D" is appended as a new record to the "BASE" I also wanted to change a few things about the Start/End Date columns

• I wanted to change the End date of the Cat's first record to the day before we actually changed it, we changed it on January 5, 2017 so the value there must be January 4, 2017
  
• Also, for the Cat's new category we would like to see the Start Date as January 5, 2017 while end date would be default to 1/1/2030

There is suppose to be a screenshot here, but I got a message that there should only be 5 attachments per post so I merged the two screenshots and displayed it in bullet #5, see screenshot for this bullet there.

5. Hahahaha, as I was typing I am seeing another possible trouble... What if I changed the Cat's category again on January 6th?
I need to find the latest instance or the most recent change of that record, say record id #5 and not record id #2 for changing
the end date. I don't want to end up changing the end date of record id #5.

Maybe another screenshot would help illustrate what circles in my mind this moment.

[NTC]

what you seek to do is a bit advanced and complicated for a forum type reply; essentially one must set up a series of queries for the purposes of comparing and updating information as per your logical chain of events - - and then trigger them appropriately with VBA code.

It is very do-able and many organizations have to import data routinely as part of their business process - and during the import logic is required to control what records actually get imported. But your 5 steps could involve several queries each to handle correctly so it would be difficult to provide a comprehensive answer in a forum format.