Data from muliple tables

[carstenhdk]

Hi




I have to tables:
Students: Personal data obout the students.
Teachers: Personal data about the teacher.

One of the feilds in Students is the name of the teacher. The detailed information of the teacher is in the Teacher table.

I want to make a report (based on this quiry), that shows contact infomation of the students and their teacher. The teachers name, mailadr etc is shown at the top, and then his students name and information below.

The only connection between these two tables is the teachers name. But when I try to make the quiry, the rest of the teachers information is not the correct data. It is some of the others teachers information. Why is that? I can´t seem to find the missing link.



Thx in advance.

[ConneXionLost]

Hi,

Post the SQL View of your query.

Cheers,

[carstenhdk]

SELECT Students.Surname, Students.Name, Students.ContactList, Students.Teamnumber, Students.Holdleder, Students.TeamStart, Students.CourseEnd, Students.[E-mail-adresse], Students.Telefon_mobil, Students.Telefon_fastnet, Students.WorkWanted, Teacher.Name, Teacher.Surname, Teacher.Telefon, Teacher.Email, Teacher.WorkWanted
FROM Students, Teacher
WHERE (((Students.ContactList)=True) AND ((Students.Teamnumber)=[Forms]![Teamnumber]![TeamnumberShow]));


I have tried to translate the words, so you could have a fair chance of see your way throgh.

The teams teacher is mentioned in the students table. The rest of the data from the teacher is in the Teacher table. But the connection is somehow lost... It mixes the two, so the detailed information of the teacher is not according to the name shown...

[ConneXionLost]

The one thing that seems to be missing from your query is a join between your tables. You have a join to a form, presumably for limiting to one team? But without a join to the teacher table, the query would just give you "everything" from that table.

Do you have Primary keys in your tables? If so, then you could use them to solve this problem by using the Primary key from "Teacher" as a foreign key in "Students". This join would need to be identified in the query.

Cheers,