Importing and modifying exisiting primary key data

[Mehul]

I am creating a simple database that tracks the GPA records of each students.
Every semester i recieve a excel file with information about the students ID
and GPA for that semester. I want to import the excel file with their
information and update the existing information for each students in the
access db. Specifically i want to fill in the GPA information into existing
column in the table that I have created for each semester ( i have created a
field for future semesters separately). Access does not let me do that as it
violates the primary key which is the student ID.

Is there another way to import and update my table. approach that i have
tried is to import the data into a temp table each semester and try to update
the master table using this temp table. But i dont know how to do that too
using sql code as the fields are variable each semester.

Master_Table
ID Fall 08 GPA Spring 08 GPA
1 Yes 3.5 (to be updated) (to be updated)

Temp_table
ID Semester GPA
1 Spring 08 3.6

So the information i get is temp table. Depending on the semester value, I
need to locate the field in master table which in this example is spring 08
and update the GPA corresponding to that semester.

If i can directly update the master table using import, it would be good. Or
else is there another way?

Please help me

Thanks

 

[tina]

your Master_Table is not designed correctly. it stores data in field names
("Fall 08", "Spring 08"), which violates normalization rules. i'd say the
data that's coming in from the Excel file is already in perfect form: a
field which uniquely identifies the student the record belongs to (ID), a
field which identifies the "group" the record belongs to (Semester), and the
value you're tracking (GPA). just set up your Access table with the same
three fields; i'd add a field with Autonumber data type to serve as the
table's primary key. then just dump each semester's Excel file into the same
table.

hth

 

[Mehul]

first of all what do u mean my violating normalization rule.

secondly, if i dump the data for each semester into the giant table. I will
have more than one record for each student. When i create a form, I need to
have information of that student in one page with the semester the student
has attended and the gpa that he/she has got in each semesters separately.
Is it possible to so that?

 

[tina]

if you have a table that holds data that describes each student, such as
StudentID, FirstName, LastName, etc., then keep that table, no problem. just
don't put semester GPA data in it. create a separate table, as i described
previously; it will be linked to the student table by the StudentID field in
each table. you can view/edit the records in the two tables by creating a
form bound to the students table, with a subform bound to the semester GPAs
table.

suggest you read up on relational design principles if you're not familiar
with normalization rules, primary/foreign key pairs, etc. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth