Problrmd with simple IF calc
I'm building a db of high school student assesment data. I have a table with basic student biographical/contact data and a related table called "transcript" of all courses and grade each student has taken at our school. Each record in the trascript table has a field (course_level) that describes if the course is 'standard' , 'honors', or 'AP'(advanced placement).
Here's my issue...
I'm trying to create a new field in my student bio table that says 'Yes" or "No" if a student has ever taken at least one honors level course. I want to do the same for AP courses as well. I'm currently using a simple 'If' calculation but running into problems. Here it is...
If ( Transcript_09::course_lev = "H" ; "Yes" ; "No" )
I think it has to do with the one to many relationship between these 2 tables.
I'd like for it to scan all courses the student has taken and insert a 'Yes' if a single 'H' appears.
My calculations are pretty rusty. Any help is much appreciated.