I recently posted and was promptly answered (thanks) how to put a many to

many relationship in Access. (Each student can have many quizzes and each

quiz can have many students...so, {tblStudent} |Student_ID

(PK)|FirstName|(etc.)| {tblQuizzes) |Quiz_ID

(PK)|QuizName|QuizDate|QuizGrade| {tblStudentQuizzes} |StudentQuiz_ID

(PK)|Student_ID (FK)|Quizzes_ID (FK)| )



Now that that has been implemented in Access (YouthOneStopData.org is the

database) but I am confused as to how to implement the update in ASP.

(Scenario) A student (with Student_ID in a session variable named

Student_ID) takes the quiz named TourQuiz on Tuesday, October 9, 2001 and

earns a score of 92. How do I put the results into the database? I have two

tables that I have to update (tblQuizzes & tblStudentQuizzes).



Thanks again for the help,

Jon

Code snippet to insert quiz results:

<%

strSQL = "INSERT INTO tblQuizzes (QuizName, QuizDate, QuizGrade)" & _

	" VALUES ('<name>', #" & Date() & "#, <grade goes here>)"



cn.Execute strSQL



Set rsQuiz = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT @@IDENTITY AS QuizID"

rsQuiz.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly



strSQL = "INSERT INTO tblStudentQuizzes (Student_ID, Quizzes_ID)" & _

	" VALUES (" & Session("StudentID") & ", " & rsQuiz("QuizID") & ")"



rsQuiz.Close

Set rsQuiz = Nothing



cn.Execute strSQL

%>



Note that using @@Identity is the fastest way to get the autonumber for

Access. See 

http://www.adopenstatic.com/experiments/fastestautonumber.asp

(thanks Ken Schaefer)



regards

David Cameron

nOw.b2b

dcameron@i...

> I recently posted and was promptly answered (thanks) how to put a many to

> many relationship in Access. (Each student can have many quizzes and each

> quiz can have many students...so, {tblStudent} |Student_ID

> (PK)|FirstName|(etc.)| {tblQuizzes) |Quiz_ID

> (PK)|QuizName|QuizDate|QuizGrade| {tblStudentQuizzes} |StudentQuiz_ID

> (PK)|Student_ID (FK)|Quizzes_ID (FK)| )

> 

> Now that that has been implemented in Access (YouthOneStopData.org is the

> database) but I am confused as to how to implement the update in ASP.

> (Scenario) A student (with Student_ID in a session variable named

> Student_ID) takes the quiz named TourQuiz on Tuesday, October 9, 2001 and

> earns a score of 92. How do I put the results into the database? I have 

two

> tables that I have to update (tblQuizzes & tblStudentQuizzes).

> 

> Thanks again for the help,

> Jon

> 



I don't think you have your tables quite right.



tblStudent contains info. about a student.

tblQuizzes contains info. about a quiz.

tblStudentQuizzes contains info. about the quizes students take.

Therefore tblStudentQuizzes should contain the quiz result of a student, 

not tblQuizzes. Move the column QuizGrade from tblQuizzes to 

tblStudentQuizzes and you only have one table to update.



Regards



Jon