Hi Everyone,



Thanks for taking the time to read this. I'm currently building a Database from scratch. This is my first time using Microsoft Access and I've been using it full time for about a week now. I do have related experience in in working with Databases in the past - mostly with NoSQL - however SQL is coming naturally to me so far!

The database's purpose is to keep track of student records for registering and attending courses. In the Database, I have three "main" tables/objects - Students, Courses, and Lectures. Lectures have a 1 to many relationship with courses - specifically, one 'course' can have many 'lectures' and each 'lecture' is joined with exactly one course. A course is said to be 'completed' when all of its subordinate lectures have been attended. Each student has a unique student id which is native to the data and used as a primary key. The lectures and courses are given auto generated primary keys.

Students are given the option of 'registering' for a course/set of courses via an infopath form. When the students fill out the form, an entry is made in the students table with their native id, if it is not entered already. When a student registers for a course, the information needs to be sent back to access and the DB needs to be updated accordingly. Since we only record whether or not students attend lectures - not courses - my current DB structure has a 'tblLectureRegistrationAndAttendance' table, which has the following columns: "ID, Lecture Id, Student Id, Attended?" Which are datatyped accordingly: Number, Number, Number, Checkbox. Each entry in this table means that the student represented by the id is registered for the lecture represented by lecture id.

What I'd like to do is design this such that when the Student fills out the InfoPath form with the set of courses they would like to register for, the Access DB receives that data, creates the student entry if necessary, and queries for the set of 'Lecture Id's that the student needs to register for by 'Course Id's, and creates the appropriate entry in the 'tblLectureRegistrationAndAttendance' table.

I'm stuck here. How do I bring the InfoPath data to Access in such a way that what I just described is possible? How do I program Access in such a way that it does what I just described?

Thanks again