Update field1 if Null, if Not Null update field2

[mfirestorm]

I am importing a data extract with grades into a student database and using that to update a "Grades" Table. I need to do this every term. This table is then presented in a nicely organized Form. I have no problems doing this for required courses, which I made their own separate columns for (ex. Course1-Term, Course1-Grade).



The problem is that there are also three electives the students are required to take. These electives can be taken from other programs or even other schools, and as a result it would be impossible and messy to have columns for every possible course.

I thought it would make sense to organize that section a bit differently. The problem is in figuring out how to update the table with my data extracts.

I can get data extracts in Excel which are organized like this (each is a column):

Student ID
Extract-CourseCode
Extract-Semester
Extract-Grade


In my Access Grades Table I have columns by these names:

Student ID

Elective1-CourseCode
Elective1-Semester
Elective1-Grade

Elective2-Course Code
Elective2-Semester
Elective2-Grade

etc.


"Solution" 1
I could link fields from the Extract-Electives to the fields for Elective1 and run an update AND ask it to only fill in fields which are NULL. Then I could take the remaining Extract-Electives and run another update for Elective2, and so forth.
The problem with that is that I can find no easy way of determining which electives were already inputted into the Grades Table and which were not. Does anyone know how I could find that out? Better yet to automatically delete entries from my Extract which were used to update the fields for Elective1?

"Solution" 2
Another solution I can think of (but also cannot implement) is to ask the Update Query to
- update Elective1 fields if they are Null
- if the Elective1 fields are Not Null, then update Elective2 fields if they are Null
- if the Elective2 fields are Not Null, then update Elective3 fields if they are Null
- and so on for about 5 electives altogether (the students need 3 electives but a couple might take an extra or two)
This is pretty much the same thing as for the first "solution" but "automated".

It is also important to note that some students take two or three electives in one term. As a result their unique student ID's (which I am using to link the Extract to the the Grades-Table) are repeated in the Extract.


Can anybody help me solve this problem? I need to warn you that I am not very proficient with Access in general, and especially not with SQL and VB. Please explain in terms a complete newbie could understand!

If my proposed solutions are completely asinine then I beg you to please present a different solution!

I've been trying to think of a way to accomplish this for weeks, I would be very grateful to anyone who could help me!!

[hertfordkc]

would go away if you reconsidered your table design. In other words, have a student table with data uniquely associated with that student, e.g. name, birthday, parent..., another table for courses, and a table which linked this stuff together. Google normalized database tables.
If you go this route, it becomes much easier to tailor your queries to produce what you need on your forms and reports. Also much easier to update.

[mfirestorm]

Ok, thanks, I'll try to normalize the database (will be tough to redo it with almost 400 students already inputted with a LOT of information, but I think it will be worth it in the end!)