How can I create this access form?

[MikeMikeCH]

Dear Community

In my database are several different course instructors and a list with topics for which we need instructors to give seminaries about it.

I would love to make a list in access (query or report) that shows all the seminary topics and for each topic the name of the course instructors that could give a seminary about that topic.

Unfortunately I really do not know how to do that. Therefore I made two pdfs. One with the table structure (table structure.pdf) that is the foundation for the list and another with an example of how the list should look like. (List or report I would like to have.pdf)

Could somebody tell me how to handle this?

Thanks a lot!!

Mike

[PhilOfWalton]

Mike, you need 3 tables, some of which you have got, but as your English is so good, I will hive the English version. These tables show on the essentials, and obviously you will need to add additional fields.

TblTutors
    TutorID          AutoNumber       Key
    TutorFirstName   Text     ' You may want to make a Unique Index
    TutorLastName    Text     ' of First & Last Name
 
TblTopics
    TopicID         AutoNumber        Key
    Topic           Text              Unique, Required
 
TblJoinTutorTopic
    TutorID         Long              Combined Key
    TopicID         Long              Combined Key
    Remark          Text      ' Maybe something like experienced, beginner
 

Obviously you set up a relationship between the matching field names.

With this scheme any Tutor can have an infinite number of topics he can teach (and if you want some sort of rating as to his experience in the topic) and conversely each topic can have an infinite number of Tutors

Phil

[MikeMikeCH]

Dear Phil

Thank you for your answer! In my situation, I have all the tables that you have mentioned except the table "Tutors".

In our database, each person can be a tutor. Whether or not a person is a tutor, you can find out in the table "Kontaktdetails". In this table, each person can be a tutor of at most 4 topics. This combination is somehow my table "Tutors", however it is more complicated. Do you know a way how to include a new table "Tutors" in my structure or how to solve this problem?

Thanks a lot and greetings

Mike

[PhilOfWalton]

I suspect that your T_Kontact is the name as my TblTutors.
The suggestion I made allows any number of D_Referencetenthmas that you like.

I'm not at all sure (Language problems) that there is any need for the T_KontactDetails. Can a single contact have more that 1 set of details?

Again I suspect that Spracht is something to do with languages spoken & Interesse is something to do with interests. If this is correct, it should be handled in the same way as the Topics, with a table of languages, a table of interests and Join tables to join those to the contacts.

So again it then becomes very easy to find all the French speakers, all the English speakers, all the sailors, and again no limits as to how many languages or how many interests a Kontact has got.

Phil

[MikeMikeCH]

Dear Phil

Thank you very much, probably the table Kontaktdetails is really not necessary, I'll have to check that.

If I have the possibility to set m:n-relationships from "Kontakt" to the "interests" or the "Referententhema (Topics) etc., could I insert a list with unilimited number of "interests" or "Referententhema" in my contact form in which the enduser of the form can chose data (e.g. interests) from the existing table and add new data that will be automatically saved in the table? (please have a look at the attached form)

Mike

[PhilOfWalton]

Ah the difference is that the main form is for the contact, and there will be continuous subforms for the interests, languages etc.

As to your question, yes, an unlimited number of interests, languages, Topics and anything else that you want.

I'm not sure what the difference is between D_Interesse_GF and D_Interesse_1, but they would all be in the same table. If there is some significant difference, you just need an indicator in the Tbl_Interests to flag that it is more important.

Here are a couple of images, the first is on a Member (Kontact) form showing 3 continuous subforms, for Boats, Spaces & Interests

As you see, there are still Combo Boxes to select the interest.

The only relevant column as far as you are concerned is the Interest in the first column.

The second is the reverse of the Interests Subform, where the main form is for Interests (Cruising Member) and those Kontacts who have that interest

The only relevant column as far as you are concerned is the Name (Kontact) in the first column.
Phil

[MikeMikeCH]

Dear Phil

Thank you very much for helping me, for all the details about the Yachting-DB and for the time you spent for helping me!!

I wish you a wonderful day.

Mike