Many-to-many structure correct?

[Sean O'Halloran]

I’m struggling to fix a database for my state agency. Here’s what the social workers need:

The agency holds several Foster Parent training sessions a year. Each session consists of 9 classes. We need to track ‘student’ attendance at each class. We also need to track class dates for each student.

The fly-in-the-ointment: ‘Student’ foster parents must attend the 9 classes, but they can fulfill this requirement over several sessions. They can take classes 1, 2 and 5 in Session 1, classes 3, 4, 7 in Session 2, and so on.

Here’s what I’ve created:

tblSessions
SessionID
SessionLocationkey ( to tblLocations; irrelevant to this post)
SessionStartDate
SessionName

tblClasses
ClassID
SessionIDkey
ClassTopic
ClassDate

tblAttendance
ClassID
StudentID

tblStudents
StudentID
StudentFirstname
etc.

The Attendance table is the junction table for the many-to-many relationship between Students and Classes.

Is this the correct structure? Thanks in advance for any advice. And thanks to Pat Hartman, The Doc Man, and SJ McAbney for getting me this far with the advice I found in researching this topic.

Sean

 

[FoFa]

It would appear the SessionIDkey in tblClasses should be taken out, and a ClassIDkey added to tblSessions because it sounds like your classes are static while your sessions change. The tblAttendance appears correct except I think I would tie it to tblSessions instead of tblClasses along with the change above.

 

[Sean O'Halloran]

Thank you for the response. Wouldn't your suggested changes make it harder to calculate which classes a student attended? What I can't seem to confidently visualize is the way to track class attendance dates for students that attend classes in more than one session.

I appreciate you taking the time to advise me.

 

[FoFa]

What about this or maybe I am not understanding the issue 100%?
tblSessions
SessionID
SessionLocationkey ( to tblLocations; irrelevant to this post)
SessionStartDate
SessionName
ClassIDkey

tblClasses
ClassID
ClassTopic
ClassDate

tblAttendance
SessionIDkey
StudentIDkey

tblStudents
StudentID
StudentFirstname
etc.

SELECT S.StudentFirstName,C.ClassTopic, SS.SessionName, SS.SessionStartDate
From tblStudent S
Inner Join tblAttendence A on S.StudentID = A.StudentIDkey
Inner Join tblSessions SS on A.SessionIDkey = SS.SessionID
Inner Join tblClasses C on SS.ClassIDkey = C.ClassID
Order by S.StudentID, SS.SessionStartDate

 

[Sean O'Halloran]

More likely I'm not communicating the issue clearly; I've fried my brain staring at the table structure. Thank you for your suggestion; I need to study the implications of your query. I'm not that facile with 'inner joins'. Thanks - Sean

 

[Sean O'Halloran]

What about this or maybe I am not understanding the issue 100%?

No, FoFa, I think you understand it better than I do. After spending a few minutes I see the logic of your structure and I'm going to try it your way. Thank you for taking the time to lay it out for me. I'll let you know how it goes.

Gratefully - Sean

 

[Sean O'Halloran]

Dear Pat,

Yes, session = semester; which I don't think was clear in my thread-starter; that probably misled FoFa. I tried the suggested changes, but kept coming back to my posted structure. One of my frustrations was this: each session/semester ALWAYS consists of 9 classes, so I kept wondering if this situation allowed me to denormalize the structure. I decided to avoid that temptation, but I'm now struggling to make data entry easy for the person who assigns the class dates and times.

I would like the screen to display the class number (1,2,...9) and class topic ("Intro to Care", etc.), and allow the user to assign the 9 class dates. I thought of using a Continuous form, but I don't know how to do two things to make it easier for the user and avoid data-entry errors: limit the available records to 9, and force the combobox looking up topic names to show the next topic as new blank records are generated.

Any advice on Forum threads to search regarding those things? Or another approach I might try? Thank you for your help in this, as well as for the many times your clearly voiced counsel has helped me in the past five years.

Sean

 

[Sean O'Halloran]

Wow that was exciting

A few years back I was visiting my 84-year-old mother at her home just outside Washington, DC, when a front came through and kicked up a tornado – very rare in that area. I’d never experienced one but knew some of the signs. I took Mom to the basement. She kept asking why we couldn’t stand near a window and watch the show!

If I’d been alone I probably would have done just that…powerful weather reminds me that all my worries are just ones and zeros bouncing to and fro in a beige box.

Hope you don’t have any storm damage. I’ll closely study the database example you posted before asking questions. Thanks again. - Sean

 

[Sean O'Halloran]

The sky brightens after the storm...wonderful solution, Pat.

To anyone else following this: By assigning a variety of 'faux' field names in separate queries to the same field in the table, Pat's subform design makes it appear that each datasheet column is a different field in the same record, when actually it creates a different record.

I need 9 class records, but I don’t want the user to have to repeatedly re-enter the same class topic name: this structure automates the topic name entry. More work for me = less work for the user; I love it.

ADDED 6-29-07: THERE WAS A PROBLEM WITH THE LINK THAT PAT INSERTED IN HER MESSAGE ABOVE. HERE IS A WORKING LINK TO THE "BoundDenormalizedForm.zip" WHICH CONTAINS THE DATABASE DISCUSSED.
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=11716&d=1132710000

>>> Advice for novices like me: Pay close attention to the Before Update and After Update coding for the text boxes on 'sfrmdetails'; the coding which makes this fly. My experience was that the "join-all" query returned a "Recordset Not Updateable" message when I tried to add / edit data in the query, and I thought I had frotsed up Pat's design, but everything worked well once I built the form and amended the code to match my naming scheme. Pat, any comments?<<<

Bridge Players Know All the Tricks – and make them look easy!

Smilin’ Sean