An append query based on data in an excel list?

[kate10123]

Hi there,

I have a table called tbl_student with student ID and several other fields.

I have recently received an excel spreadsheet with a list of student IDs and their courses.

How would I go about running an append/update query to update each student with no course if they exist in the excel list?

Just trying to save data entry!

Thanks

Kate

 

[KathyL]

First you import your excel spreadsheet into a new table.
2nd, make a copy of your tbl_student for backup.
3rd, decide whether you’re appending new students or updating old students, or both.
4th, Use a query to match spreadsheet table to student master table, use criteria of <> to find records that don’t match, then turn that into an append query, and append it to your master table.
5th, write an update query to update student records with data from your spreadsheet table.

 

[kate10123]

Thanks I will give that a try

 

[kate10123]

Hi,

I imported the excel table into my database called 'tbl_Enrolments'

Then I tried to create an UPDATE query but am not sure on the correct syntax.

I want to update the field called 'course' in table 'tbl_Student' with a corresponding match in tbl_Enrolments WHERE the studentID matches.

 

[Kiwiman]

Howzit

Try the below

UPDATE tbl_Student INNER JOIN tbl_Enrolments ON tbl_Student.StudentId = tbl_Enrolments.StudentId SET tbl_Student.Course= [tbl_Enrolments]![Course];

Holding the course information in the student table is fine, as long as the student can only ever enrol in one course. How do you store your information should they enrol in multiple courses?

You may want to consider another table to hold this information, with at least the following:

tbl_StudCourses
StudCourseID [PK] Autonumber
StudentID [FK] - Primary key from table tbl_Student
CourseID [FK] - Primary Key from table tbl_Courses


This way a student can enrol in as many courses as they want, and you can report on who has enrolled in each course easily. You can put a unique index on the CourseID and StudentID fields combined to ensure the same student does not enrol in the same course.