I have a relational database which in the first table lists information about teachers at a school and in a second table lists the subjects taught at that school.

A teacher can teach many subjects and a subject can be taught my many teachers.

I need to create a report that lists the teachers for each subject and a query that allows a user to select a teacher and then displayes all the subjects taught by that teacher.

I thought creating a database would be very easy but I was mistaken, this probably seems very simple to all you but I need help (I am using FMPro 3.0 on Mac OS 9)

Thanks alot,

Sam

Welcome to the Forum,

You have a vey old version FileMaker. You have a many to many relationship. This is usually solved by adding a third file called a join file. You make a relation from the Teacher file to the new file using Teacher ID as the key or match fields. You make a relation from the Subject file to the new file using Subject ID as the key or match fields.

You will need calculated fields in the join file to transfer information between your 2 existing files. For these calculations you will need to make relationships to these 3 files using the same fields as the key. Some calculation examples are Teacher Name = Relationship::Teacher Name, Subject = Relationship::Subject.

Let us know if you need more help.

Ok I think I get what you mean I will try tomorrow and see how it goes. So I will end up with 3 tables/files rather than the 2?

Do you know of any FREE tutorials of FileMakerPro (preferably v.3) on how to do more complex tasks such as scripting, etc...

Thanks alot for your help

I still cannot do it when i go to define relationship it askes me to select another FileMaker Document/file. I have the 2 tables in one file (2 different layouts at top left ot screen)

Plz help

FileMaker 3 through 6 normaly has one table per file. Relationships are from file to file but you can have self-relationships where the origin is the same as the destination. In you case you should have 3 files (tables): Teacher, Subject and TeacherSubject (the join file or table).

Teacher should have a field TeacherID, an auto-entered serial number. Subject should have a field SubjectID, an auto-entered serial number. TeacherSubject can have a field TeacherSubjectID, an auto-entered serial number and must have the fields TeacherID and TSubjectID.

Make relationship in Teacher to TeacherSubject TeacherId = :TeacherId

Make relationship in TeacherSubject to Teacher TeacherId = ::TeacherId

Make relationship in Subject to TeacherSubject SubjectID = ::SubjectID

Make relationship in TeacherSubject to Subject SubjectID = ::SubjectID

You will need to make anuber of calculated fields in TeacherSubject for example:

TeacherName = Teacher::TeacherName

SubjectName = Subject::SubjectName

These are necessary to pass information about the teacher to subject and information about suject to teacher. FileMaker 3 can only see one relationship away, this is a work around sometimes called tunneling.

Okay I think were I was going wrong was with the TeacherSubjectID Ill try again tomorrow. You have been more than helpfull, thanks alot