I don't see what a relationship has to do with your value list. Normally, a relationship is used to create a conditional value list and that relationship then "filters" the list of values to only display a subset of the total list of values. But you haven't described any such "filtering" in your original post.
This relationship looks incorrect:
_kf_PeriodID_1 = __kp_PeriodID AND
_kf_PeriodID_2 = __kp_PeriodID
that relationship will only match records in the two tables if _kf_PeriodID_1, __kp_PeriodID AND _kf_PeriodID_2 all have exactly the same value.
I'm sure I am making this harder than it really is.
With the1st and 2nd Period's I was attempting to figure out how to fix the many-to-many relationship. I moved it around and i'm not sure yet if it makes sence. Like the example that is given where you add the 'Line Item' table to fix the many-to-many relationship between the product and the invoice. A student will have more than one period (usually 2) and a period will have a number students (usually around 20-30). I attached my new attempt at the relationship graph. The 'Period_1st' and 'Period_2nd' are copies of the 'Period' table. A student can only be on 1 service per period but they do more than one period in a year, therefore they do more than one service (in Facilitator table)...
I setup a form associated with the 'Student' table. I have 3 fields in the form that I want to be a dropdown list from other tables.
1. Student's Name (got this)
2. Period the student is currently on which will be populated from the 'Period' table. On the form I want it to read something like, "Period 1 (6/13/13-8/1/13)
3. would be the service the student will be on during the period selected. This would be taken from the 'Facilitator' table which has a service field.
This is what I used, though it doesn't work, to set up the 'Period' drop down list.
I selected 'student_PERIOD::__kp_PeriodID' to where the data should come from. Then in the value list I selected 'Period' and the field 'PeriodYear' which is a calculation to combine two fields together (i.e. Period & "(" & PeriodStartingDate & "-" & PeriodEndingDate & ")" )
On the Relationship graph I have the names set-up to where the all caps is the orginal table and the lowercase is the table that I am connecting it to. They are also color coded. the student tables are in green, period tables is in purple, etc.
Hopefully this makes sence. Thank you so much for your help!
A many to many relationship usually requires a join table between the two tables linked in the many to many relationship.
Period::__pkPeriodID = Service::_fkPeriodID
Student::__pkStudentID = Service::_fkStudentID
Thus, each record in Service, the join table, links a specific record in Period to a specific record in Student. Since you have a different Facilitator for each service, you'd link them by FacilitatorID to each record in Service as well.
A portal to service on a Period based layout would list all students and their specified service for that period. A portal to Service on a Student layout would list all service assignments for that student.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
How can i connect them so I can use this form for data entry? The Students, services, and periods will already be loaded into the system. I want to use this form assign the students to the periods and services.
A portal to the Join table is most frequently used for this purpose--though other methods can also be used. Please check out the demo file as it shows the "basic setup" for such a portal as well as a layout with a different interface option that simulates a check box list for selecting items.