Help with translating a SQL Query to an FM find request

[LivingRoutes]

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, 2007 by Guest
Topic name lost... best guess

[Lee Smith]

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

[bcooney]

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

[Thomas2syllables]

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.