The first question:
1. This question actually says: "Select all the data, if fall or summer is in the description, write to the empty field the year from the term start date field. Then if spring, insert the same date data as above, but subtract 1 from the date. If none of these match, simply carry over the acad_year field.
The second question reads:
1. Select all records from all fields with data (named specifically) and pull the row number (which is not shown in the fields) and order them all by effective date (effdt) and description (desc), and insert into ranking field the data from the CS_Subject_tbl, as long as it is not a duplicate (eft_status = 'A') so for ease of working out the data, eliminate ALL duplicates right off the bat. The only duplicate is the very last record. You would end up with an alphabetically and chronologically sorted list, first by date, and if there is a duplication in date, then by description.
After rechecking that, it looks like there is not a duplicate, it is 7/1 vs 7/2 on the date, close but not quite...working on the table...
Table...it does not look like line number is ***** in the output, but I would throw a line number field on there just in case...
Q3 is: @ID
Question 4: Runtime errors are caused by not handling an output somewhere, in this case the problem is in the "On" statement, there is no subsequent "then" to handling if the records do not match.
it is there further down in the code, but it also does not handle "if field not empty" on inserting.
And finally an example star schema, these are very simple relation tables diagrams, and my instructor in college often took referenced diagrams in lieu of making new ones from scratch. If you need one of these made, I can do it for you, for a slight fee.