If you specify in value list setup, that your values be sorted by the value in field 2, values with duplicates in field 2 will automatically be omitted from your value list.
One option is to define an auto-entered calculation on a text field that combines the first and last names in a single field. Since this is a text field, you can also set a unique values validation rule on this field to make sure that you don't get two students with exactly the same name. You can then use this field as the field 2 value in your value list.
Please note that much of the time, field 1 should list a student ID number, not the student's last name due to the possibilty that student last names may not be unique and sometimes a last name is changed (even with children)--both situations can really complicate the function of your database if you select names instead of a never changing always unique ID number.
Thanks for your reply. I understand what you are saying about the ID number and the danger of using names. The problem I have is that the people inputting the data don't know the student ID but they know the name. So when an entry is added for a student I have to use their names in the value list so that the student ID gets populated by the relationship in the tables.
Do you have any more information about setting up an auto-entered clculation field? Again, I appreciate your help very much.
That's why you list the name in column 2. The user selects by name, but the value list enters the ID number from column 1. If the text in column 2 is validated to be unique in all cases, the number need not even be visible in the value list, you can just have a alphabetical list of names.
Do you have any more information about setting up an auto-entered clculation field?
What "more information" do you need here?