2 Replies Latest reply on Jun 27, 2015 1:38 AM by misterg65

    A join table ... or import and looked up values.



      A join table ... or import and looked up values.


      Our school gives us data for the pupils from an Integrated Management System, via Excel files, although XML output is available. The data I need to use is on two separate spreadsheets. The first contains data such as Special Education Needs, English as a Second Language, and Reading Age. The second table measures progress through the year, based on termly reports, covering effort and attainment. The only common link is the name of the student. 

      I need a summary of some data from each table. My idea was a join table, which would have to be based on StudentName (thus requiring me to check for duplicates), pulling in the data from the other two tables. However, I cannot see a way of entering all the names at once. This led my to believe that I would have to import a list of names first, then add fields that look up values from each table as required.

      Is there a more elegant solution? I am trying to make the best of a bad job. The idea of this intermediate table was to use this as something I can portal into (probably not the right term, but I couldn't think of a better one!)

      Thanks in advance


        • 1. Re: A join table ... or import and looked up values.

          I don't think this meets the "textbook" definition of a "join" table as it appears that you want one record for each unique student in your "join" table. You can omit duplicates as part of the import records process.

          For that studentname field, you can specify field validation options of "unique values" and "validate always". Then you can import data from either or both excel files into this table and names will only be imported once. Subsequent duplicates will be omitted automatically--producing a table of just one record per student name.

          But you'll need to look out for the possibility that you might get two students with the same exact name. (And student names can change on you too--such as when a child undergoes a step parent adoption and takes the last name of the adopting step parent.)

          • 2. Re: A join table ... or import and looked up values.

            Thank you for the reply. As for the "Unique Values" validation, would two students with the same name but in different classes be seen as Unique, or does it only work on one field?

            For example is John Smith in Class 1 seen as a different person to John Smith in class 2?