1 Reply Latest reply on Mar 30, 2012 10:19 AM by philmodjunk

    comparing values from two tables - Inner Join



      comparing values from two tables - Inner Join


      I really need help! I have to present a FMP and Access solution in a couple days. I have Access working, but can't get FMP to work. (ironicly I love FMP, but not Access). I need to report a comparision of student test results from two different years. Here is the setup.

      3 tables:  Student Demographics, 2010 test results & 2011 test results. The test results have reading, math & science scores.

      Key field: Student ID 

      I want to only show the reading scores for each student when the student had results from both years. So if a student had a 2010_test:reading_score and 2011_test:readingscore, print the student name the the two reading scores.

      Any help will be greatly appreciated.


        • 1. Re: comparing values from two tables - Inner Join

          I would use two tables for this--one for student info and one for test results of all years--with a field to identify the year. Do you plan to add a new table each year for your test results? That's not the greatest idea for an Access solution and can be really problematic for a FileMaker database.

          Working with what you have for the moment, you appear to have these relationships:

          StudentDemographics::Student ID = 2010Results::StudentID

          StudentDemographics::Student ID = 2011Results::StudentID

          But what I don't know is if the "results" records have a separate records for each subject or if you have separate fields for each subject within the same results records. Which do you have?