Update a field from another table where both tables are the many in a relationship

[suzeg]

I have tired so many itterations of querys I need help!

I have one table 1)tblStudentRecords - A student may have many records. This table contains ID field and CourseID field and others.
The second table is 2)tblVouchers - a Student may have many vouchers. This table contains ID field and VoucherNo field.

I am trying to assign the CourseID from tblStudentRecords to the field CourseID in the tblVouchers. Because both fields have many of the same IDs I cannot get the Course Number to update correctly.

I have tried creating junction tables to no avail as the results will always repeat the same voucher number for the students records. For example

ID CourseNo
1402 85050
1402 85050
1402 85050

Should be

ID CourseNo
1402 85050
1402 85654
1402 85657

Any help would be sincerely appreciated as I have spent many many hours on this problem.:banghead: thank you

 

[plog]

You've lost me with your vague use of 'ID' and non-specific references to your tables' relationships. You reference 'ID' which I think is the Student ID (althought its not named that), you also reference a CourseID as well as 'Course Number' in your explanation, but in your data you have 'CourseNo'. Then somewhere in there is VoucherNo. And your example data didn't help much because new values magically appeared for CourseNo without providing the source they came from.

Can you list your tables, their fields and some sample data from each. Format it like this:

TableNameHere
FieldName1, FieldName2, FieldName3
"Sample", 1/1/2013, 17
"Data", 1/2/2013, 26

Then provide what you want the result to be based on that sample data.

 

[suzeg]

Sure. I apologize.

TblStudents
ID,Fname,Lname
1402,John,Smith

tblStudentRecords
ID,CourseID,datestart,datefinish
1402,85050,1/1/12,2/10//12
1402,85657,3/15/12,3/20/12

tblVouchers
VoucherNo,ID,CourseID,dateissued
123-abc-pqy,1402,(this is what I need),2/28/12
555-878-oku,1402,(this is what I need),3/15/12

tblCourses
CourseID,CourseTitle,Price
85050,MsWord,195.00
85657,MsExcel,195.00

ID is the student ID in tblstudents and tblStudentRecords with a 1 to many relationship.
tblVouchers contains ID (the Student ID not a unique field here) the key field is VoucherNo.
tblCourses has key field CourseID.

Hope this clears it up some.

 

[plog]

First, tblVouchers should not have the field ID, it just needs CourseID. Second, I see no way to determine what CourseID goes with each Voucher. How do you determine which CourseID goes with each Voucher?

 

[suzeg]

That is my problem. The data was originally entered without assigning a voucher to a CourseID just a studentID that is why ID is in the Voucher table. This wasn't my design, I am revamping an old database and was hoping to fix that.

I think at this point, all I care about is if student 1402 has taken 2 classes and has been issued 2 vouchers. I could update that student with the 2 vouchers. So I would end up with

tblVouchers
VoucherNo,ID,CourseID,dateissued
123-abc-pqy,1402,85050,2/28/12
555-878-oku,1402,85657,3/15/12

Then from this point forward I would require a CourseID when a Voucher is assigned to a student.