The "system" that you can download here illustrates this concept. The system asks the user to enter a specific student number using an Input box. The system seeks the student's record in the student information file. It then finds the first of the student's records in the course file (which is a query). The user can then "scroll" through the student's course records.
We have two data files. First, there is a "Student Information File" that has information about students. It has the Student Number as its primary key. Next, we have a query, "CourseQuery", that is created to list all courses being taken by all students. Look at the query for more specific information. Because it is a query, there is no key field and no guarantee of uniqueness of each record. Property 3 cannot always be met. This causes a problem with property 5, so two things must happen. First, the data in the query MUST be ordered, that is, sorted on the field that logically defines what we are trying to do. We are looking at a specific student's course records, so the data MUST be sorted in student number order. This system will not work if the data are not sorted (try it).
Look at the program code. First, the "seek" method is used to find the specific student in the student information file. Next, the "findnext" method to locate the student's first course record in the query. The "seek" won't work because the query doesn't have a key field. The student number is saved in an internal variable, "studnosave". All of this is done in the cmdSeekStudent_Click() subroutine.
Once the student has been found and the first course record has been found, the user can click on the "scroll" button to "scroll" through the student's course records. This done in the cmdNextCourse_Click() subroutine. Look at the logic of the subroutine. Every time the user clicks the button, the "movenext" method moves to the next record in the query. If the student number of the current record in the query matches the value in the "studnosave" variable, the record is displayed in the text box. If the current record in the query doesn't match the saved student number, then the user has "scrolled" all the way through the student's course records and the message that no more courses for the student appears. This logic works ONLY because the data are in order by student number.
Why, you ask, is this concept important? Well, you will (I guarantee it) run into situations where it simply isn't practical to have "well-structured relations" that have key fields and are nicely developed. Being able to develop Q&D (that's Quick and Dirty, a very technical MIS term) tables of data without keys, then having a program with appropriate logic handle them will come in handy. The logic is simple, and this scenario pops up all the time. Remember, the only way to create a "many to many" relationship is through an associative entity (file), and Access does this very well with queries.
The links to each component of the system are below. They are all quite small and should download quickly. Please let me know if you have problems.
You will have to change the file path for both files in the data control objects on the form to match your computer's path. Otherwise, it should work with no problems.