Question about tables (1 Viewer)

[Scolton]

Hello,
I wil introduce myself i am a dutch student. At the moment i am working on a database but i ran into a problem that i can't get my head around.
Lets say i have 3 tables
Table 1 : Has students in it
Table 2 : Has their groups
Table 3 : Has their classes they follow.

Now i want i want to create a query. Which connects a student with a group and then shows me all the classes he has to attend to.
So lets say i have a search form (which i created) i search for the student (i can get this far) then i want it to show me the groups he/she is in and to show me the classes he/she follows per group.

Thanks in advance and sorry for my bad english

 

[CJ_London]

you need to learn about relationships - here is a link to get you started

http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

so to answer

Which connects a student with a group and then shows me all the classes he has to attend to

you need to decide if a student can only belong to one group, or whether they can belong to many groups. Which way you go will dictate what tables you require, what they need to contain as a minimum and how they link together.

Similarly, the relationship between groups and classes - is a class unique to one group, or can it apply to more than one group?

Answer these questions and reading up on relationships should show you the way forward, but if not, provide the details from your tables and we can take it forward

 

[plog]

the classes he/she follows per group.

English was great, so nothing to apologize for. However the relationship you described between groups and classes is confusing. If you could post your relationship between those 3 tables it would help.

I fear you've got them over-related. In your table explanation it sounded as if Groups was directly related to Students. But in your last question it sounds like Groups are directly related to Classes. Which is it?

You can only pick one direct relationship. Can you better explain what groups are? Again, posting relationship screen shot would help.

 

[Scolton]

Hmmm i think i didn't explain it correctly.
You can see it as this:
Table 1 students (includes al students with a unique primary key) and has a one to many relationship with Table 2.
Table 2 has Groups in it.
Table 3 you can see as a subgroup of Table 2 because it has all the classes in it.

lets say Group 12B has english and dutch as classes in it.
Group 14B has science and math.
Group 15A has Gymclass and German.

Now lets say student A is in Group 12B and follows English but can also be found in Group 15A because he follows Gymclass.



Student B is in group 14B and has science and math. He/She is also in group 12B and follows Dutch

Now i want to be able to have a query on which i can build a search form.
Which shows me that Student A is in group 12B and following English in that group. But he is also in group 15A following Gymclass. Same goes for student B

I hope i explained it abit beter^^

 

[plog]

Now lets say student A is in Group 12B and follows English

This isn't consistent with your structure. With the structure you've just explained Classes->Groups->Students, if a student is in 12B he is automatically related to English and Dutch, not just English.

How can you tell that student is just related to English? Can you show your table relationship screenshot?

 

[Scolton]

I understand that he would automaticly be related to English and Dutch.
But Student A only follows English and i want to see that he is in group 12B and see that he only follows English

So actuely i want him to only be related to english in group 12B and not English and Dutch is this possible?

Lets say Student C only follows Dutch in 12B then i want to see that he is in group 12B and only follows Dutch.

At this moment i cant take a screenshot sorry. Wil upload one later on.

 

[plog]

With that it sounds like classes are directly related to students then and not groups. They can be limited by groups, but they are directly related to students. You will need 5 tables for this, the 3 you have mentioned and 2 junction tables (http://en.wikipedia.org/wiki/Junction_table).

Junction tables establish a many-to-many relationship between 2 tables. You will need one between Students and Groups and between Students and Classes.

 

[Scolton]

completly forget my other "story" i want to create this.
Table 1 has all the teachers in it.
Table 2 has the study's they teach
Table 3 has the subjects they talk about.

How can i set this up relationship wise.

you can find a screenshot in the attachement.

i stil want to achieve the same as in the other story
so i want to be able to search for a student then see which study's he/she teaches and then i want to see the subjects he/she talks about.

 

[jdraw]

Please help us understand your statement:

so i want to be able to search for a student then see which study's he/she teaches and then i want to see

Students don't teach ---teachers teach??

the subjects he/she talks about.

is this teachers or students.

Please clarify the requirments.

 

[Scolton]

completly forget my other "story" i want to create this.
Table 1 has all the teachers in it.
Table 2 has the study's they teach
Table 3 has the subjects they talk about.

How can i set this up relationship wise.

you can find a screenshot in the attachement.

i stil want to achieve the same as in the other story
so i want to be able to search for a teacher then see which study's the teacher teaches and then i want to see the subjects the teacher talks about.

my bad ^^

 

[Scolton]

oke somehow i have figured it out^^
thanks for the help.