Form doesn't show the info I want sometimes

[SamMaynard]

Situation:
I have two tables one called Students and the other called Student_Offers.

The Student table contains information about the student and has a unique identifier called StudentID which is an auto number.

The Student_Offers table has details of the offers of employment we make to students. The way I associate the offer with the student is a field called StudentID (exactly the same as the Student table unique identifier) by using a lookup for the data type. This field however is not primary key in the Student_Offers table.

I have a form that shows the Students Table Information and a sub form with the Student_Offers Table information all combined onto one form.

Problem:
The form will not show students who haven't been given an offer. To further clarify, if a student has an offer then that student shows up on the form. If an offer has not been extended to the student he or she doesn't show up on the form.

What I want:
I want all students to show up on the form regardless if an offer has been made or not.

What I have done that hasn’t worked so far:
I have gone to the relationships window and changed the join types. I’ve tried all three, none have worked.

I have tried to make the StudentID field in the Student_Offers table a primary key, hasn't worked.

And I have tried to see if enforcing referential integrity would help.

Any help would be much appreciated. Thank you, Sam

 

[bat17]

What should work is the Join type. Select so that you get all of the records from the student table and then make sure that the student ID from the Student table is in the fields that you have selected to show.

HTh

Peter

 

[SamMaynard]

Thank you Peter for the thought. As mentioned in the original message I have tried the join type and it hasn't seemed to work. Funny Huh! It should work, that's why this on has me stumped.

Any other help would be appreciated.

Sam

 

[bat17]

I would work at the query level first until you have your data sorted. Reduce the number of fields to just the StudentID from the Student table and set the join to show all records from the student table then start adding field and testing to see where it goes wrong. This should help you find the problem.

peter