Import Text Names when Numeric Field

[Wutz]

Hi there,

I have a list of students and grades in Excel to import into
Access2007; however, in the Excel file, the student names are text and
in the Access table tblGrades, the student name field is numeric
because it's actually linked to the autoassigned numeric primary key
StudentID in tblStudents. Is there some way to import the text names
without having to match and then manually change the student name to
their numeric ID first? I'll be importing several hundred records
every quarter and I'd rather not have to manually change the names
from text to their ID each time. I appreciate any help!

Thanks,
Wutz

 

[Jeanette Cunningham]

Wutz,
there is no really easy way to do this.
The first problem is what do you do if you have 2 students with the same
first name and last name - how will you know which ID is the correct one?

You can import the excel data into a new table.
Create a query using tblStudents, include the first name, last name and the
StudentID and the new table with the imported data.
Join the tables on the first name field and the last name field.
You will also need to check if there are any names in the imported data that
don't exist in tblStudents. Use an outer join to do this.
When you are satisfied that you have matched each record in the imported
data with its corresponding Student name, you can run an update query to add
the new grades to tblGrades.

Jeanette Cunningham