There is no direct relationship between either table. They are BOTH dependent on the parent table.
Whatever relationship each one has, it has. But they have no relationship to each other - unless you explicitly build one in.
You COULD (stress could, not should) add an autonumber field in the one you import first, then make that field a foreign key in the one you import second. Find the first-imported record with the proper student ID and copy the autonumber field of the first-import record to the corresponding field of the second-import record. That's how you would CONSTRUCT a relationship. As you describe it, that wouldn't violate any rules - except a very basic conceptual rule, a normalization issue. TECHNICALLY, and this is only if you are a real stickler of a purist, that relationship cannot exist at all, because it depends on information not stored in either table as a true "prime" key.
The student ID number is the REAL link and the only place that exists as a pure "prime" key is in the student table.
Now, the reason I mention the technical feasibility of this approach is that if you have a one (student)-to-many (term) relationship, you will have many term records for one student. For data entry purposes, you might wish to "turn the tables around" and drive a report off the student table (as a lookup). If so, it would make sense to have a key to help you look up the suppemental information in the second-import record and know exactly which second-import record goes with which first-import record.
Therefore, you have found a rarity - a time when it makes sense to violate strict normalization procedures. I went through the explanation above so you would realize that while you can build such a relationship as you noted, it is not a preferred operation due to that technical violation of key dependency rules. Only the fact that the two tables are one-to-one with each other makes this at all possible. If the second table had ALSO been one-to-many, this would have been a hopeless mess.
