Help need with tables and relations, please

[tblues87]

Hello, I'm new here and also with Access 2007 so I have question.

I need to make database for one school. First I made one table with persons who attend school and I have ID number for each person and it is primary key. Second I made table with foreign languages which school has.

Next I made From for first table and added button to send me to form of second table with languages.
My question is:
is it possible, when the first form is complete and pressed button to go to second form, that ID number automatically copy's to second from without additional enlisting.

 

[jzwp22]

How are you trying to relate the student with the foreign languages? Are the foreign languages actually foreign language classes that the students enroll in or are you trying to document which foreign languages each student speaks?

In either case, you need a third table that relates the data from the other two tables.

 

[tblues87]

The first table is like this:
ID
Name
Date of birth
...

Second table is:
ID
Language
Degree
Module

Can you please tell me how to define third table

 

[jzwp22]

It looks like the language table holds degree programs (maybe?) for each language. So I assume that you want to define which students are participating in those programs. If that is the case, then the third table would look like this:

tblLanguageProgramStudents
-pkLangProgStudentID primary key, autonumber
-fkLanguageID foreign key to tblLanguages
-fkStudentID foreign key to tblStudents

I would recommend that instead of just calling the primary key field as ID to use a field name that is a little more descriptive. I usually use a field name that is similar to the table name and preceed it with pk and end it with ID. The pk denotes primary key and the fk denotes foreign key. The foreign key fields should be a long integer to match the datatype of the autonumber primary key field

Also, it is best not to have spaces or special characters in your table or field names as this will force you to enclose every name in square brackets in queries and in code. Also, certain words like name, date, year, month are reserved words in Access, so it is best not to use them as table or field names.

 

[tblues87]

Thanks, I make it work but it isn't practical.
You need to know every LanguageID to connect with a student.
Because there is:
LanguageID=1:
-Language: Engl
-Degree:A1
-Module: I

LanguageID:2
-Language:Engl
-Degree: B1
-Module: II
...

and I have eight languages and every language has six degrees (A1,A2,B1,B2,C1,C2) and four modules (I,II,III,IV).

Can you give me some advice how to arrange that.

Thank you

 

[jzwp22]

Since a language can have many degrees that is a one-to-many relationship. Additionally, since a degree can have many modules that is another one-to-many relationship. Furthermore, since a particular degree designation (A1, A2 etc.) can apply to many languages you actually have a many-to-many relationship between languages and degrees. The same can be said for the degrees and modules. Applying the rules of normalization to these relationships, you need more tables to properly define each language program

So reworking the table structure

tblLanguages
-pkLanguageID primary key, autonumber
-txtLanguage

tblDegrees (just holds each degree A1, A2 etc. as records in the table)
-pkDegreeID primary key, autonumber
-txtDegree

Now we need a table to relate each possible degree with its language

tblLanguageDegrees
-pkLanguageDegreeID primary key, autonumber
-fkLanguageID foreign key to tblLanguages
-fkDegreeID foreign key to tblDegrees

Note: all foreign key fields must be a long integer number datatype field

Now a table to hold a list of the modules (I,II, etc.). Each module designation is a record in this table

tblModules
-pkModuleID primary key, autonumber
-txtModule

Now we need a table where we can relate each language/degree combination with each module

tblLanguageDegreeModule
-pkLangDegreeModID primary key, autonumber
-fkLanguageDegreeID foreign key to tblLanguageDegrees
-fkModuleID foreign key to tblModules


Assuming that each student needs to be tied to a specific language/degree/module combination and also assuming that student may participate in more than one of these combinations (one-to-many relationship), this is how we would set up that:

First our basic table to hold the student info:

tblStudents
-pkStudentID primary key, autonumber
-txtFName
-txtLName

Now relate each specific Language/Degree/Module combination to the specific student

tblStudentLanguageDegreeModule
-pkStudentLangDegrModID primary key, autonumber
-fkStudentID foreign key to tblStudents
-fkLangDegreeModID foreign key to tblLanguageDegreeModule

 

[tblues87]

Can you please check if I done it right, because I can't figure it out.
If you can, please add a form to enter data because I can't figure it out.

Thanks

 

[jzwp22]

Unfortunately, I only have Access 2003 here at work so I cannot open your database. I can take a look at it from home tonight.