Member Avatar for azapovjednik

hello all,

I want to make some simple database in access that would track student information, semesters etc...

I have created basic tables like: students, professors, subjects.. now I am stuck with the most important part: semesters and subjects...

here is the link Click Herewhere you can see database relationship diagram example (had to do it again in English lang for your convenience)

Probably I did it all wrong, but here is what I thought and imagined:
The idea is to have a form/subform
in the main form to select or enter academic year, semester and dates
in the subform to be able to select subjects from subject table, its professor from professors table, and retrieve some information regarding subjects... and also add some extra information...

later after that is done.. I would like to connect students with these semester/schedule ... but that is for later

I hope you can shed some lights on this topic? I am a newbie in this. I would like to know how to implement all that in forms? is my diagram wrong? how to connect all those tables into a form? thank youuuuuu

I appreciate any help

  • 2 Contributors
  • 1 Reply
  • 368 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by Shahbaz_2

Dani AI

Generated

A likely missing piece in the diagram is an "offering" (or section) table that represents a subject taught in a specific term by a specific professor. Treat "Subject" as the catalog entry (MATH101, Title, Credits, Description) and create a separate table for the term-specific instance (Term + Subject + Professor + room/schedule/notes). That keeps the design normalized and makes the main-form/subform pattern work cleanly. This also aligns with 's plan and complements 's suggestion to prototype and iterate.

Suggested core tables and key fields:

  • tblStudents: StudentID (PK AutoNumber), StudentNumber, LastName, FirstName, ...
  • tblProfessors: ProfessorID (PK), LastName, FirstName, Email, ...
  • tblSubjects: SubjectID (PK), SubjectCode, Title, Credits, Description
  • tblTerms: TermID (PK), AcademicYear, TermName (Fall/Spring), StartDate, EndDate
  • tblOfferings: OfferingID (PK), TermID (FK), SubjectID (FK), ProfessorID (FK), Section, Room, MeetingTimes, Notes
  • tblEnrollments: EnrollmentID (PK), OfferingID (FK), StudentID (FK), EnrollmentDate, Grade

Form implementation notes (fits the main-form/subform idea): make the main form bound to tblTerms (academic year + semester). Make the subform bound to tblOfferings or to a query that joins Offerings->Subjects->Professors so subject title and prof name appear directly. Set Link Master Fields = TermID and Link Child Fields = TermID. In the subform use combo boxes for Subject and Professor (RowSource = appropriate table/query, bound to the ID). To show extra subject info in the subform, include those fields in the subform query or expose them as extra combo columns and read them with the combo Column property (column indexes start at 0) in form events.

Troubleshooting and cautions: enforce referential integrity in Relationships, use AutoNumber PKs and matching FK data types, avoid Access "Lookup" fields in table design (prefer combos on forms), and base subforms on queries rather than relying on DLookups for performance and clarity. If rows do not appear in the subform, check the subform RecordSource and that the Link Master/Child fields reference identical field names and contain matching values.

Member Avatar for Shahbaz_2

best way is to do this..

Make some thing.. and if you got problme.. discuss it here...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.