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