Is there anyway to have a column in a report be a query?

Our MSSQL system was originally based on an IBM unidata system which has multivalued fields, so we have some serious structural issues...

I'm at a collage and trying to run a report that shows all our current students, not really hard.

But when I go to add a column that shows if they are a resident I run into problems. That table stores their residency status for many years and I only want to see the current semester. My only choice is to show all the semesters and then clean up in excel, because when I put in a select for the semester all of the commuter students disappear.

So is it possible to have a column be a query or formula that goes out and returns the residency status for a specific semester