Question populating database dynamicly

[Hakello]

Hello!

I'm creating a database where results of tests for students can be saved. To do this I have a database where a student can be assigned to a specific education. This education is coupled to a number of subjects which in turn have a number of tests.

I have made a form in which someone can double click a student which opens up another form where he can mark results for all tests coupled to the education that particular student is following. This report however, only shows tests that have already been created as testdata. So now I'm looking for a way to populate this form with the proper tests.

Problem is, I have no idea what the best way is to do this. I assume one way would be to create a button in this form that will create the appropriate tests (look at education of student, get subjects and tests and then create results for those) and then do a requerry, but I don't have a clue on how to do this. (the creating part, a requerry shouldn't be much of a problem anymore )

Does someone have an example or some advice for me?

If interested, my databasedesign: (not allowed to post links or images )
img7.imageshack.us/img7/2416/databaseontwerp.png
(education = Opleiding, subject = Vak, test = Toets, result=Resultaat)

Much, much appreciated!

 

[DavidAtWork]

In what form do you currently have the test results? Ideally you'll have them all listed on a spreadsheet or some other tabular form but in order to be able to populate your DB tables, you'll need to create the ID's that will link the data. These ID's are as in your DB table design, for example it sounds like from the above each test record will need a pupil_id, subject_id, testType_id. If these don't exist on the spreadsheet, you'll need to add columns for these ID's and then edit the cells with the correct ID's.
With all that in place you can import the spreadsheet and then run some 'append' queries against this new raw data to populate the required tables.

David

 

[Hakello]

Hello David, thank you for you reply, I'll try to clarify the situation a bit:

The tests are not based on a spreadsheet, users are able to create new [Test]s with buttons elsewhere and link this to a specific [Subject]. This subject is then linked to an [Education].

When a teacher wants to put in a [Result] he clicks on a [Student] and should see a pop-up(form) with all tests this [Student] should follow according to his [Education].

The problem is that these [Result]s do not yet exist and thus do not have an ID yet and don't show up in this form. The teacher is now unable to mark tests unless he first creates a [Result] for a particular [Test], which is a lot of work to do for each [Student]. I would like to automate this creation process.

I'm quite the novice on access, so I might completly miss some basic features. An append querie sounds good and should work for me... I think , I'll go and search how those work, thanks!

 

[jdraw]

Hakello,

There is a tutorial and database that may help you with database and Access.
Here is the link http://www.rogersaccesslibrary.com/Tutorials/ClassInformation.zip

Good luck.

 

[Hakello]

Thank you jdraw, however I don't think my database design is the issue at the moment but reading up a bit on database design never hurts

I've found out what I need to do, however I could use some help in the coding department. This query gets me the information I need, namely all tests coupled to a certain education filtered for a specific student.

SELECT Student.studentindex, Toets.toetscode 
FROM (Koppelvak INNER JOIN Toets ON Koppelvak.vakcode = Toets.vakcode) 
INNER JOIN Student ON Koppelvak.opleidingid = Student.opleidingid 
WHERE (((Student.studentindex)=" & studentindex & ") AND ((Student.opleidingid)=" & opleidingid & "))

So I've created a beautiful button that can be clicked to update the tests

Private Sub btnToetsen_Click()

Dim sSQL As String
Dim toetsQuery As String
Dim studentindex As Integer
Dim opleidingid As Integer

    studentindex = Me.tbStudentindex
    opleidingid = Me.tbOpleidingid

    toetsQuery = "SELECT Student.studentindex, Toets.toetscode FROM (Koppelvak INNER JOIN Toets ON Koppelvak.vakcode = Toets.vakcode) INNER JOIN Student ON Koppelvak.opleidingid = Student.opleidingid WHERE (((Student.studentindex)=" & studentindex & ") AND ((Student.opleidingid)=" & opleidingid & "))"
    sSQL = "INSERT INTO Resultaat (studentindex, toetscode)" & toetsQuery
    MsgBox (sSQL)
    
    DoCmd.RunSQL (sSQL)
    DoCmd.Requery
      
End Sub

Well you can guess where this goes wrong, there is no WHERE NOT EXISTS so if you click the button again it simply adds all the tests, again (PK is autoincrement). But I keep getting a syntax error if I try to get it in, does someone know what the correct syntax should be (or maybe what would be a smarter way to achieve this)?