Hi All,

I have two tables: Students and Programs. Students fields are: StudentID, ProgramID, Status; Program fields are: ProgramID, year, term. I want some way to see all students whose status is "Cancelled" for a given year/term pair. In SQL I think I'd do

SELECT *

FROM Students S, Programs P

WHERE S.Status = "Cancelled" AND S.ProgramID = P.ProgramID AND P.term = "Spring" and P.year = 2007

to get all of the students who cancelled a spring program from 2007. Can anyone help me figure out how to find/display these records in filemaker?

Thanks in advance.

Edited November 28, 200718 yr by Guest
Topic name lost... best guess

Most likely you are being messed up by the Year search. If this field is an actual Date, you would need to put a range in it to pull out the Records (i.e. [color:blue]1/1/2007..12/31/2007) or, create a field called [color:blue]Year_P_Year (or something, that uses the Date Function [color:blue]Year (P.year), Format it as a Number, and then you can do use a find like [color:blue]2007

You should also avoid using things like the "." in your naming functions. I use a [color:blue]_ for spaces, or periods, except for the file extension of course.

HTH

Lee

I think that you need a third table, a join between students and programs, "Enrollment," perhaps?

You should also avoid using things like the "." in your naming functions. I use a _ for spaces, or periods, except for the file extension of course.

What he gave was an example of a query written in SQL, which uses dots to separate table names and column names, and he asked how to translate that into Filemaker.