Importing new data

[Ray239]

I'm currently using Access 2010. I'm trying to import new data into school database from excel, but I'm encountering some problems. I received thousands of students' mobile phone number with their student ID. This school database has a "student" table that includes name, student ID, address, enrollment information and home phone number.



The student table already has student ID as primary key so I want to be able to import new data such as mobile phone number into existing table. I received an error message that said "the records you pasted contain primary key values that already exist in the destination table". I don't want to spend time finding students by their ID and inserting new data one by one.

How do I insert new data into student table with existing primary key values? The excel I'm trying to import only has two columns, student ID and mobile phone number. I already set up new empty field called Mobile Phone Number in student database. It didn't work.

Thanks.

[June7]

So some students and their ID's are already in the table and you need to update their phone number and other students are new records?

This requires two SQL actions - INSERT and UPDATE.

Set up a link to the Excel sheet then use it like a table (except can't edit data). You can use the link in queries and to build reports. You can use it as the source for SQL INSERT and UPDATE actions.

[Ray239]

Thank you for the reply. I think SQL UPDATE action is the best way to do it. What are the steps to do it? I tried to search for tutorials that show you how to update data based on primary key in Access, but no luck.

EDIT: Is this an example of SQL update?

UPDATE Students INNER JOIN NewSheet
ON Students.StudentID=NewSheet.StudentID
SET Students.PhoneNumber = NewSheet.[PhoneNumber];

[June7]

Yes it is.

[Ray239]

Thanks. I think it works.