Multi Loop sql statement

[naruponk]

Hi,

I have 3 tbls

Student : StdID,Name

Score : Name, Subject, Score

Summry : StdID,Name, Score, Subject

suspose each fields and each tbl has so many records.

I want to insert into tblSummary which depends on...
- I need to loop until score is end of that fileds then insert StdID
to summary

- I mean StdID in Summary must also have every subject
of tblScore.

This is my coding (unsucceeded)

Do While Not rsScore.EOF
Do While Not rsStd.EOF
wp = rsStd.Fields!StdID

comtxt = "Insert Into tblSummry (StdID,Name)" & _
"Values('" & wp & "','" & rsScore.Fields!name & "')"

upCom.ActiveConnection = cn
upCom.CommandType = adCmdText
upCom.CommandText = comtxt
upCom.Execute
rsStd.MoveNext
Loop
rsScore.MoveNext

Loop


can anyone help me?
thanks!

[ahara]

If I read your problem correctly, you want to insert the student ID along with the Name into the summary table, but it does not come from the Score table, it comes from the Student table. Using two recordsets the way you have will never work. The only possible way it could work would be if the two tables had the exact same rows with the name and id fields perfectly synched - which would never happen. In relational databases, we use tables that are related - and the relations are usually defined using foreign keys:

A foreign key is a particular field of a particular table that is also in another table (very simple definition)


These two tables are related:


Student : StdID,Name <-----'Name' is a foreign key to the table Score

Score : Name, Subject, Score

The 'Name' field links these two tables together, although if you designed these tables, you should consider making the foreign key StdID....so the Score table would look like:

Score: StdID, Subject, Score

This is because it is quite possible to have two students with the same name, whereas an ID field is unique (and easier and faster to query)...but let us assume you have to work with the current data structure....rather than generating two record sets, you only need one, but using SQL that joins the two tables:

SELECT
Student.StdId,
Score.Name,
Score.Subject,
Score.Score,
FROM
Student, Score
WHERE
Student.Name = Score.Name

Use the sql to generate one recordset, loop through, and you can insert id, name, and subject and score as well (I figure this is what you are really after) - hope that helps

cheers

[naruponk]

Thank you for your reply I will try it.