Can't get this relationship to work

[muskee]

Hi guys!
I made a database for a university. I have the following relationship

Student --< Enrollment >--- Program ---- Dates

Student table has the students info. The enrollment table has the program the student has done, what grade he got and the date he did that program. Here is how it looks like:

Enrollment table

         Name                   StudentID               Program         ProgramID         FinalGrade                 Dates
    1)   John Smith       10000                      Calculus 1            calc-01                 4.8                    01/01/2017 - 01/04/2017
    2)  Bob Doe               10001                      Calculus 1            calc-01                 4.8                   01/05/2017 - 15/05/2017

Program table:

Name                        Id               Type             
Calculus 1              calc-01         Course

Dates table:

DateId    ProgramId    Duration          weeks    dates
  100           calc-01          100 hours        4            01/01/2017 - 01/04/2017
   101           calc-01          50 hours          2            01/05/2017 - 15/05/2017

 

As naturally, a Program can be opened during different dates in the year.
The problem I get is with the dates. I can't pick a specific date I try to enroll a student to a program. I can't relate it to the proper date, they all get related to the data in the first date, in this case dateID: 100.
To get the dates is easy, with a drop down list that shows only related records to Programs.
In the enrollment table, if I pick the date with the ID 101, nothing happens, I can't relate the dates table so the student can pick the proper dates the course is in.
How can I fix this?

Reallyyy appreciate your help!

[Ocean West]

make a new Dates table occurrence and attach it to enrollment by the program ID from there you can create a drop down list once you choose the program you can select the corresponding dates. - however that wouldn't prevent users from picking dates that had already passed.

Ideally instead of a drop down picker use a popup with a portal or a card window where you are presented a list of Dates sorted by Program and omitting past dates then when selecting it would set all the needed fields. 

 

 

[muskee]

Ok, I did that...
Related the IdProgram from enrollement with IdProgram from DatesOcurrence.
I created a field called GetDates with the drop down list and it filters correctly to show only the related dates to a program.
I pick the corresponding date but I don't get the corresponding information (duration, weeks, etc),
do I need to create another field?

[comment]

2 hours ago, muskee said:

I have the following relationship

Student --< Enrollment >--- Program ---- Dates

If a Program has several instances (what you call Dates), and a Student enrolls into a specific instance of a Program, then obviously your basic TOG should look like this:

Students --< Enrollments >-- Dates >-- Programs

 

In order to pick the Program first, then the Date, define an auxiliary relationship as:

Enrollments::ProgramID = Dates 2::ProgramID

and use it to create a value list of related DateIDs, starting from Enrollments.

 

 

Edited August 29, 2017 by comment

[muskee]

24 minutes ago, comment said:

In order to pick the Program first, then the Date, define an auxiliary relationship as:

Enrollments::ProgramID = Dates 2::ProgramID

and use it to create a value list of related DateIDs, starting from Enrollments.

 

 

I did that but I'm confused.

First I get the program ID from a value list in the programs table

On which field should the value list be on? I have it like this Enrollments:: dates -->have the value list on this field
I currently select the program ID and then select the dates but I don't get the information accordingly

[comment]

41 minutes ago, muskee said:

On which field should the value list be on? I

The second value list should be attached to the Enrollments::DateID field, which is the matchfield to Dates. 

 

41 minutes ago, muskee said:

I currently select the program ID and then select the dates but I don't get the information accordingly

Make sure you get your information from Dates, not Dates 2. 

[muskee]

20 hours ago, comment said:

The second value list should be attached to the Enrollments::DateID field, which is the matchfield to Dates.

You mean like this?

[comment]

No, I mean like this:

[muskee]

Thanks guys, solved my problem!