Importing data from Excel

[ek77]

Hi all

First of all, I apologize for being long winded and the ridiculousness of this request. Not to mention the difficulty I have trying to explain it.



A little background - Data is pulled from an ERP system to an excel spreadsheet on a daily basis. It is then slightly modified (some records change, others are removed or added) then copied and pasted over one of the many tabs in another spreadsheet. This is the data that reports on the other tabs use. During meetings there are notes being made for some of the entries but since the data is always changing, those notes need to be moved or retyped each time. Assuming I'm explaining this well enough, you can see the problem at hand. Here's a couple key pieces of information.

- The data in the excel spreadsheet does not have any fields that can be used as a primary key in access by themselves. A record can be considered unique based on 2 or 3 fields used together. In order to use a primary key access would have to assign one during the import.
- I am unable to get a direct connection to the database that stores the information.

I was thinking that the data could be imported to an access database which would allow any notes made to be in a separate table preventing them from being overwritten or lining up to the wrong record. The problem I am running into is that when importing the data from excel to access, I need to find a way for it to only overwrite the records that have changed while retaining the connection to any notes that have been saved to it. My initial train of thought was to create a linked table to the spreadsheet that is updated daily, create a second table mirroring the data in the linked table. When the data is changed on the linked table it would be compared to the a mirrored table and update only the changes while making sure that any notes made for a record are retained.

Side note - I have considered using PowerPivot or another application for this task as I'm not sure access is the right solution.

Any help would be greatly appreciated. I've spent quite a bit of time researching and trying to find a good solution with not much luck.

[June7]

If 2 or 3 fields can be used to provide a unique identifier, then save those values into the 'Notes' table as foreign key. This is a compound key (I don't really like them but if must, then must). So is it 2 or is it 3 fields? This must be established and must be consistent.

[ek77]

I believe 3. I will confirm this and be sure that it is established and consistent as you mentioned. Hopefully having this compound key will solve the problem at hand. Thanks for the response!