Adding Exam To All Students.....

[ardy]

Hello All:
I am trying to build an access applet for my wife who is a K5 teacher. I am fairly new to Access and like to think I have fairly reasonable understanding of the relational databases. I however am not a programmer. I am not done with the applet yet so please don't judge it as is, I still have a lot of work to do…… What has gotten me stump:banghead: is how to add an exam for all students. Student ID is the Key field that relates all tables. I have a button in the main form(frm_Student_List) for adding a test, What I envision is once clicked by the user the form Add Test(frm_Test_Add) opens, Once the user adds all related information in the form by clicking the close and save the code will add the test to the Grades table for each student, so if we have 20 students(Students table), the Grade table will have 20 new record that has the same GradeID.
I hope I have explained this OK, I am attaching my Database and the Relationship report for your viewing…… I am assuming we have to use a For statement but am not sure how to construct it so it. I also am very much so open to advise in an over all Schema.

Regards
Ardy

 

[pbaldy]

Can't look at the sample yet, but I'm thinking an append query that gets values from the form but selects from the students table. That would be much quicker than a loop in code.

 

[ardy]

Thanks for the reply.......
not being strong in coding I was thinking of the same thing, but still couldn't put it together, because the append needs to take all student's ID's and the same Test ID and append........ I also am not sure which is faster the code or query or it's the same..........

 

[pbaldy]

The query would be faster, though for a small table it wouldn't be that significant. It would look like:

INSERT INTO TargetTable(Field1, Field2, Field3)
SELECT Field1, Field2, Forms!FormName.TextboxName As Whatever
FROM StudentTable

which would get 2 fields from the table plus a value from the form. Yours of course might look different, but that's the concept.

 

[ardy]

OK... This is good start.... I have constructed this in the sql window of query design if I click the design icon to see what it did it gives me the error.

INSERT INTO Grades(C_S_ID, GUSD_Student_ID, Type, Total_Score, Date)
SELECT GUSD_Student_ID, Forms!frm_Test_Add.Type, Forms!frm_Test_Add.Date, Forms!frm_Test_Add.Subject_ID, Forms!frm_Test_Add.MaxScore
FROM Students

The ERROR is ---->.Syntax error INSERT INTO statement

 

[pbaldy]

Date has to be bracketed because it's a reserved word (and thus not a good choice as a field name). Also, the fields in the SELECT must be in the same order as the INSERT line (that associates what field goes where). You'll see that when you get it in design view.

 

[ardy]

Yes you are right, I keep forgetting abt the reserve words.....I changed it to

INSERT INTO Grades ( GUSD_Student_ID, C_S_ID, Type, Test_Date )
SELECT Students.GUSD_Student_ID, [Forms]![frm_Test_Add].[Subject_ID] AS S_S_ID, [Forms]![frm_Test_Add].[Type] AS Type, [Forms]![frm_Test_Add].[Test_Date] AS Test_Date
FROM Students;

Added the the code to the button

Private Sub btnSaveClose_Click()
' Adding test to all students
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "Q_Test_Add"
            DoCmd.SetWarnings True
            MsgBox ("Test Added.")
End Sub

I Get the error
Run-Time error '3824:
An INSERT INTO query cannot contain a multi-valued field.

 

[pbaldy]

Sorry, there I can't help you. I don't like and haven't used the multi-value field.

 

[ardy]

Thanks.......
Been reading abt the Multivalue and how it works. interesting. seem to be an enigma between all developers and the community in general. I found it intreasting as to how Access keeps the values in it's system files and presents it graphically to the user. in any event, I had to change my schima a bit and got rid of the mutivalue, it won't be as flexible but it should work.

In any event I do appreciate your help

Ardy

 

[pbaldy]

Yeah, if memory serves it's normalized in the background, but that structure isn't exposed to the user/developer. There's a feeling among developers that several new features are more targeted at making things easy for the beginner (calculated fields, multi-value fields, etc). Most won't use them for a variety of reasons, not least of which is they typically can't be upsized if you want to move the back end to SQL Server, Oracle, etc.