Updating (overwriting) information in Access table

[tculley]

I have a table in Access linked to a SharePoint list. The table is comprised of about 15 fields whose contents are originally pulled from another data source (in Excel format). There are an additional 10 or so fields after the original 15 that make up a questionnaire (added via SharePoint) that contain answers to questions about the first 15 fields.

The data in the first 15 fields needs to be updated periodically when new data from my external source is available to download. A lot of the information will remain the same, however some of the fields within each of the rows will change and need to be updated. It is also important that the 10 fields that contain the questionnaire are not modified at all during this process.

Is there a way for me to easily update the cells that have changed using an Update query or something similar? The data does have a unique identifier column (ID NUMBER) that is present on the current SharePoint list and the external data source.

I was thinking from a logical standpoint to put the new external data into a table, find the ID Number in the SP list and new external data, compare the values in the rest of the row on the SP list to the row of the external data, and if a value is different update the cell with the value from the external data. Not sure how to accomplish this using Access queries though.

I really appreciate any help at all! If you need more information, please let me know. If you think there's a more logical way to do this, please let me know your feedback!!

[zmbd]

You haven't given us really anything to work with; however, from what little you've given us, it sounds as if your database is not normalized.

First: You need to read thru:> Database Normalization and Table Structures.

Second: you need to tell us what version of Access and share point you are using.

Third: Because you are working with sharepoint lists, when you create your tables in Access, you will not be able to use the normal means of relating tables... this is because MS is very short sighted. Instead, you will have to use the "Look-up" field at the table level. This is the ONE and ONLY time that look-up fields at the table level should be used... and that is only because of MS.

So, normalized your database-tables/sharepoint-lists, then you can begin to reliably import your updates.

[tculley]

I'm using SharePoint 2010 with Access 2007. The data in the SharePoint list has an ID Number that is the same as in the external data. They also have the same columns located within them. How could I use the Look Up fields to accomplish these updates?

[zmbd]

you still haven't given us any information about your tables.

did you read thru the link in my last post?