Flatten two tables to one

[dtheo]

I have a "Student" table with student number, last and first name. I also have a "Grades" tables student number, course number and score. I have made a set with Students=>Grades where a student will have several grades in a 1 to many relation. This works fine.

I need to create a new table that has just one flattened record per student with all the scores as part of the same record.

Eg.

Student # Last First Course1 Score1 Course 2 Score 2 Course 3 Score 3 etc...

The number of courses will vary for each student.

Any ideas how to do this?

Thanks

[Stan Mathews]

Re: Flatten two tables to one

You can get something close by creating a cross tab of the grades table.

Group rows by student number, group columns by course, choose the grade field as the summarization and specify any of the choices (first, last, max) that you want.

Then create a set with student as the parent and this result table as the child with a 1-1 relationship.

[dtheo]

Re: Flatten two tables to one

Thanks Stan... That sort of works, except that now every student's record has all the course numbers listed - not just their courses. The other courses all have a value of zero which will complicate my next step.

Ideally I would like to only have courses listed that have scores.

I even thought of exporting to excel and then trying to convert records with the same ID # to one record with multiple columns for each course and score.

[Tom Cone Jr]

Re: Flatten two tables to one

David, a problem with building a single table that will "fit" all students is that each student may have a different number of courses.

Tell us more about why you need this table?

I'm wondering if a memo field would work. It occurs to me that you could add a memo field to your Student's table, or link a new table containing only student_id and memo field (better choice). You could then use a script to step through the Grades table, passing courses and scores back to the student's memo field.

[dtheo]

Re: Flatten two tables to one

Maybe this is the wrong way to do it!

I am still working on a way to calculate honor roll for students. On a FORM I can create several calculated fields that will indicate if the stduents has made honor roll level 1, 2 or 3. These are just calculated fields. Is there a way to use the calculated fields to update a field in the parent - eg. the calculated field would update a parent field called honor roll level and fill in 1, 2 or 3.

The calculations required by our high school are:
Level 1 - ALL grades above 93
Level 2 - An average of 87 or above with no grades below 85
Level 3 - An average of 85 or above allowing only 1 grade between 77 and 84

I can generate a report that will produce the list of students, but ideally I would like to be able to FLAG or indicate (and become part of the parent) which level the student made for honeor roll.

Thanks

[Tom Cone Jr]

Re: Flatten two tables to one

Sorry. Not enough detail for me to understand the difficulty.

Perhaps the expressions you use in the calc fields could be used in a saved update operation that could be run against the Students table, after you've added an honor roll field to hold 0, 1, 2, or 3.

[dtheo]

Re: Flatten two tables to one

Due to the complexity of the honor roll rules, I would likely have to do several updates, unless there is a way to combine all the calculated fields into one.

Thanks

[Tom Cone Jr]

Re: Flatten two tables to one

David, maybe it's time to write a custom script that does it in one pass through the Students table. In pseudo code it would look something like this:

1) open both tables, set indexes in each table based on student_id
2) loop through the students table and for each student...
    - set a counter to 1
    - set cumulative score to 0
    - find the first grade
    - loop through grades until the student_id changes or eof() encountered
      --- on each fetch increment the counter and sum the cumulative score
    - when all grades are processed, divide cumulative score by the counter
       to get the average score
    - assign an honor roll score (0,1,2,3) to the student
3) close both tables

Obviously, this simple example computes only averages. However it could be easily modified to set flags or keep track of other data required by your honor roll criteria.

[JohnZaleski]

Re: Flatten two tables to one

If you have a report that already gives you the correct honors rating, you could use the report events to write the rating to the student record.You wouldn't need a set to do this , a report with the course mark records grouped by the student_id would be all that is necessary.Lin the section events there will be an event for the student id group.I assume that would be where the rating would be shown.A simple xbasic script could open that record and write your calculated rating to a field on the student record: something like tb1.honor rating = calc-> student rating

[dtheo]

Re: Flatten two tables to one

Thanks for the response. I will give that a try also.

Tom Cone wrote a fantastic script for me last night (and tweaked it at 5 this morning) that will actually calculates each of the HR levels and then inserts the correct value in the parent table. It works GREAT!

I appreciate all the help from all A5 users. Your help has allowed me to use A5 to calculate the honor rolls for over 1300 students in seconds. It used to take us several days of manually crunching numbers to get this done.

Regards to all,

Dave