Question Using Relationship

[Abadon125]

I am very new to Access. I am trying to adapt a template (Classroom Management if you search on the Microsoft Template website) to work for my specific needs.

Here is the question. There are two tables that keep track of Students and Classes. One student can be in multiple classes. I need to keep track of if payment has been received by the student for each of the classes that they are enrolled in. I obviously can not keep track of this in the student table because it would not be scalable to multiple classes and I can not keep track of it in the class table because there are multiple students.

As I said I am very new here so if someone could give just a brief overview of what I need to do that would be very helpful! Thanks.

 

[dkinley]

A junction table will work. Suppose you called this table tPayment. The core fields for the table might look something like ...

pkPaymentID (primary key)
fkStudentID (foreign key)
fkClassID (foreign key)
ClassAmount
AmountPaid
.....

Here, you would tie the specific class with the students and vicey-versa because the junction of the payment is where the class and student cross paths. You could add other fields as necessary (date paid, amount owed, etc) to track these aspects.

HTH,
-dK

 

[Abadon125]

A junction table will work. Suppose you called this table tPayment. The core fields for the table might look something like ...

pkPaymentID (primary key)
fkStudentID (foreign key)
fkClassID (foreign key)
ClassAmount
AmountPaid
.....

Here, you would tie the specific class with the students and vicey-versa because the junction of the payment is where the class and student cross paths. You could add other fields as necessary (date paid, amount owed, etc) to track these aspects.

HTH,
-dK

So would the pkPaymentID be an auto number and then set a relationship between StudentID and ClassID?

 

[dkinley]

Yes, that would be the autonumber field. This table would fall in between the other two from a relationship perspective. In this manner to find out all of the classes a student was in, you would map that through the junction. Or, what students were in each class, you would map through the junction with the end result is the junction table is collecting it all up for you from the foreign keys.

As an aside, I would give this table a more meaningful name such as Schedule, or, inline with most junction table names, ClassStudent (the hybrid of the two tables). The fiscal side of things would probably be it's own table. I do not know the complexity of what you are trying to accomplish or how long the system would be in use to properly advise, but if you do searches out there on Scheduling, you should find examples of exactly what you are trying to accomplish.

From the last post, I just wanted to give you a clear analog so you could visualize the junction table with what you had already in your head.

-dK

 

[Abadon125]

That's what I have right now but still confused. Please let me know what I am doing wrong or what I should do next. Thanks again.

 

[Abadon125]

I got it. Thanks!