Array data

[Tony39]

Hi I am lookng for some help. I have a spreadsheet with five rows of data for each student.

Student A Field1 Field2 Fieled3

Student A Field1 Field2 Fieled3

Student A Field1 Field2 Fieled3

Student A Field1 Field2 Fieled3

Student A Field1 Field2 Fieled3

Student B Field1 Field2 Fieled3

Student B Field1 Field2 Fieled3 etc

(there are 350 pupils)

I need to place the data from the five rows for each Student on one row for each student. And do this for all students

Field1 is a pupil ID number.

Does anyone have a suggestion how this could be done?

Thanks

Tony

Edited June 13, 2008 by Guest

[mr_vodka]

What do you mean by putting the data into one row? Do you mean concetenating the data? Do you mean putting them into seperate fields? Are these numbers that you need to total up?

Your description is too vague. Please clarify.b

[Tony39]

John

The file started as a spreadsheet.

Each line of the five rows per student is a subject entry. I wish to lift the last four rows and add these as separate fields to the first row. This will enable a relational link to another school file.

ie

Sudent A F1 F2 F3 F4 F5 then with F1 F2 F3 F4 F5 from row 2 to be on the same row as the first. Similarly for rows 3,4 and 5.

Row 6 is a new student and I would like to repeat this process for the next five rows

Essentially I want to move all the fields for the first five rows onto row 1 and row 6 being a new student repeat this process for rows 6-10 etc

I wish all the data for each student to reside on one row or record instead of five rows as it does on the excel speadsheet.

Is this any clearer? Thanks for any suggestions or ideas.

Tony

[comment]

It's possible, though quite tedious - especially in version 7. But before getting into this, I need to ask: are you sure you want to do this? It's still not quite clear what the data in the fields represents, but it seems the correct way to organize it would be to do the exact opposite, i.e. create a separate record for each entry, with three fields only: StudentID, DataLabel and DataValue. Then another table where each student has a unique record with StudentID and Name.

Another question: is this a one-time conversion, or do you need to do this periodically?

[Tony39]

Thanks for your reply. Yes I want to do this! The school's management system exports a report with each subject a student is following on a separate line. ie.

AdminCode StudentName Subject1 Level Grade TeacherA Target etc.

AdminCode StudentName Subject2 Level Grade TeacherB Target

Each Student has 5 subjects.The export file is given to me as a excel spreadsheet.The spreadsheet has 360 students, with each student given five rows in the spreadsheet.

However I wish to place all the 5 rows of data (for one student) into one record (row) so the I can use the admincode as a relational field to other tables of data for the same student.

I will have to do this twice a year.

I would be grateful for ideas on how to achieve this.

Tony

[comment]

This is a bit different from what I understood originally. You definitely do NOT want to do this the way you describe. You should import the records as they are, with each row creating a new record. But you need to import them into a table of Subjects? (Grades?), and make this table related to your Students table.

If there can be new students in this report, you need to import them into your Students table as well - for this, the AdminCode field in the Students table must be set to unique, validate always.

[Tony39]

I think I see what you mean. I could place the related fields in my report pulling the subject grades across using the adm Code from the subjects table. Is that the idea?

Thanks

Tony

[comment]

I'm not sure what your report includes, but usually you would do it in the opposite direction: report from the Subjects table, sub-summarized by student.

[Tony39]

Thanks

I will try the approach suggested

Tony