Populating the many side of a 1 to Many relationship

[Dave Nene]

Hi,

I have two tables. On the 1 side is Yr8students and on the many side is
Attendance. Referential Integrity is set.

I have populated the Yr8student table with 30 names and student ids. This
table is linked via its StudentID field to a StudentID field in attendance.

To start off with Attendance is empty.

I have a form which is based on a query. The SQL in this query is :-

SELECT Yr8Students.StudentID, Yr8Students.Firstname, Yr8Students.Lastname,
Attendance.Subject, Attendance.Attended, Attendance.Punctuality,
Attendance.Date, Attendance.StudentID
FROM Yr8Students LEFT JOIN Attendance ON Yr8Students.StudentID =
Attendance.StudentID;

I want to be able to use DAO to populate certain fields in attendance for
each of the 30 student records.

I have used the following code in the On Open event of the form :-


Private Sub Form_Open(Cancel As Integer)
Dim reg As DAO.Recordset
Dim dt As Date
dt = Now

Set db = CurrentDb

Set reg = db.OpenRecordset("Yr8Attending", dbOpenDynaset)

reg.MoveFirst

Do While Not reg.EOF

reg.Edit
reg!Attended = "True"
reg!Subject = "Science"
reg!Date = dt
reg.Update
reg.MoveNext

Loop
Forms!Yr8Attending.Requery
reg.Close
End Sub

The purpose of this is to set the Attendance records for a particular class
and date with all students attending, then for instance the teacher can
just click on particular records to indicate students who are absent.

The code above works but not unexpectedly when run again simply edits the
Attendance records already entered again.

Basically I want to add new attendance records on the many side for
different classes based upon date.

I have tried AddNew without success.

Some of this does appear to depend upon when the record on the Many side is
created. Is it possible to force this to happen.

Any Thoughts ?

Rgds


Dave


This works but what I really want to do is to append the attendance records
with new records based upon a different attendance date

 

[John Spencer]

First of all I would probably use an append query to do this

INSERT INTO Attendance(StudentID, Attended, Subject, [Date])
SELECT Y.StudentID, True, "Science", Date()
FROM Yr8Students as Y LEFT JOIN
(SELECT A.StudentID
FROM Attendance as A
WHERE A.Date = Date()
AND A.Subject = "Science") as Aa
ON Y.StudentID = Aa.StudentID
WHERE Aa.StudentID is Null

You might need to do the above query in steps.
First Query - Identify all those students that already have an Attendance
record for the date and subject.

Second query - Use the above query as part of an unmatched query against
Yr8StudentsTable to identify which students need an attendance record

Third query - is the the append query where you use the results of the
second query



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dave Nene]

Hi John,

Thank you for that, I was wondering if a query was the way to go.

Let me add something.

All of this was to create a register for a particular class.

The class members, e.g. the Yr 8's are all known and typically populate the
1 side.

The idea is that when the teacher needs to take the register for a
particular class he creates a form which lists all the students names on the
left with the fields from attendance to the right of those.

The information relating to the Attendance fields will be unique and only
ever relate to that particular Subject class at that particular Date/Time.

Typically students will all be marked attending, but of course the teacher
will be able to toggle those who aren't to not attending etc.

I will look through the SQL John and come back to you when I have
experimented with it.

Thank you for your time and trouble. Much appreciated.

Regards

Dave

First of all I would probably use an append query to do this

INSERT INTO Attendance(StudentID, Attended, Subject, [Date])
SELECT Y.StudentID, True, "Science", Date()
FROM Yr8Students as Y LEFT JOIN
(SELECT A.StudentID
FROM Attendance as A
WHERE A.Date = Date()
AND A.Subject = "Science") as Aa
ON Y.StudentID = Aa.StudentID
WHERE Aa.StudentID is Null

You might need to do the above query in steps.
First Query - Identify all those students that already have an Attendance
record for the date and subject.

Second query - Use the above query as part of an unmatched query against
Yr8StudentsTable to identify which students need an attendance record

Third query - is the the append query where you use the results of the
second query



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I have two tables. On the 1 side is Yr8students and on the many side is
Attendance. Referential Integrity is set.

I have populated the Yr8student table with 30 names and student ids. This
table is linked via its StudentID field to a StudentID field in
attendance.

To start off with Attendance is empty.

I have a form which is based on a query. The SQL in this query is :-

SELECT Yr8Students.StudentID, Yr8Students.Firstname, Yr8Students.Lastname,
Attendance.Subject, Attendance.Attended, Attendance.Punctuality,
Attendance.Date, Attendance.StudentID
FROM Yr8Students LEFT JOIN Attendance ON Yr8Students.StudentID =
Attendance.StudentID;

I want to be able to use DAO to populate certain fields in attendance for
each of the 30 student records.

I have used the following code in the On Open event of the form :-


Private Sub Form_Open(Cancel As Integer)
Dim reg As DAO.Recordset
Dim dt As Date
dt = Now

Set db = CurrentDb

Set reg = db.OpenRecordset("Yr8Attending", dbOpenDynaset)

reg.MoveFirst

Do While Not reg.EOF

reg.Edit
reg!Attended = "True"
reg!Subject = "Science"
reg!Date = dt
reg.Update
reg.MoveNext

Loop
Forms!Yr8Attending.Requery
reg.Close
End Sub

The purpose of this is to set the Attendance records for a particular
class
and date with all students attending, then for instance the teacher can
just click on particular records to indicate students who are absent.

The code above works but not unexpectedly when run again simply edits the
Attendance records already entered again.

Basically I want to add new attendance records on the many side for
different classes based upon date.

I have tried AddNew without success.

Some of this does appear to depend upon when the record on the Many side
is
created. Is it possible to force this to happen.

Any Thoughts ?

Rgds


Dave


This works but what I really want to do is to append the attendance
records
with new records based upon a different attendance date