Query Design

[Daryl2106]

Hi,



I have old student records that cotain Grade, Progress, StudentID and CourseNumber fields which are linked to a table with a CourseName field. I have new student records in the same table (with the same fields) with empty Grade and Course fields. I need to update the new records with the old Grade and progress data based on finding the correct old record containing the same StudentID and CourseName, only updating those where the StudentID and CourseName are the same. I am not sure where to begin with the design of this query. My Critera building skills are still rather weak.

Tahnks and take care,

Daryl

[alansidman]

You indicated that the Grade and Course Fields are empty. Did you mean that both are empty? If so, I don't see how you can match old student name and old course number with new student name and new course number. Or am I missing something here. Are there two fields for the course? Name and Number? Please clarify this issue.

[Daryl2106]

Thanks for the reply. The old records are from last years enrollment and the new records are from this years enrollment. In our program when a student doesn't finish a course by June they reenroll in the same course the following year under a new CourseNumber (sometimes with a new faculty member assigned to that course). What stays the same is the StudentID and the CourseName. The CourseName (CourseInventoryT) is linked to a table of CourseNumbers (AnnualCourseNumbers) which is linked to the StudentID (in the StudentEnrollmentTable). The fields Grade and Progress are field in the StudentEnrollmentT. They contain last years data (current Grade and Progress (% of course completed)). I need to transfer this data to the Progress and Grade fields of the new records which are currently empty as these records were created with the new CourseNumbers.

I don`t have the DB at home, other wise I would send some attachments. It seems like it should be doable, I just don`t know the criteria I need to use to match the StudentID and CourseName from last years record to the StudentiD and CourseName of the new records. It is the CourseNumbers of each record that are different.

Thanks and take care,

Daryl

[alansidman]

I was thinking that a self join might work, but that would also give you the old records linking to the old records and the new records linking to the new records so I don't think that will work. It would be helpful to see a dummied up (Remove any confidential or change confidential data) sample database. It may be easier to devolve a solution if we have something to play with.

Alan

[Daryl2106]

Hi Alan,

Thnaks for looking at this for me!

Take care,

Daryl

[alansidman]

Looking at your db now. Not sure which table you wish to update? also which fields need to be updated and with which data. Please be as specific as possible. Use an example if possible for one student.

Alan

[Daryl2106]

Thanks for the reply.

If you sort StudentEnrollmentT by StudentNumber there will be 8 records with the 0013869 StudentNumber. The corresponding AnnulaClassNumberID will be different for each one. If you look up the AnnualClassNumber in the AnnualClassNumberT, you will see that some of these AnnualClassNumbers are assigned to term 1119 (last year) and some 1229 (this year). It varies, but most of the StudentEnrollmentTable records that correspond to the 1119 TermNumber have data in the Progress and Grade fields but none in the Progress and Grade fields for those records in the StudentEnrollmentT that correspond to the TermNumber 1129.

If you also look at the AnnualClassNumbersT the CourseInventoryID is linked to one of only 74 records. (Each year the ClassNumber changes as does the TermNumber, but the CourseName remians the same. Students who have no completed a course get re-enrolled into the same course(name) but under a different CourseNumber and TermNumber. The is imposed on us by the college and we get our initial data from a PeopleSoft DB).

What I am trying to do is transfer the Progress and Grade field's data associated with the Term 1119 ClassNumber to the ClassNumber associated with Term 1129 for the same CourseInvetoryT (Course)ID. If you run the StudentEnrollmentbyFacultyQ it will return all the students assigned to that Faculty for this year (Term 1229). I need to have it display the Progress and Grade data with these records from the same course for the student from the previous year. A course they have yet to finish.

Once again Thanks. Please ask more questions if you need more info.

Take care,

Daryl

[Daryl2106]

Hi,

I managed to figure it out.

Take care,

Daryl