Student database

[oakvich]

I’m experimenting with creating a student database.

I have a table called: Student Records with the usual info, Name, DOB, Major etc.
Primary Key = Student ID.

I have separate tables for each degrees such as English, Psychology, Business etc.

This is what I’m trying to accomplish:

John Doe wants to be an English major. I go to the English Form and create a new record. I type in his student ID and his name auto populates in the English form. All I need on the English form is Student ID and name to populate, the rest of the English form has fields specific to it and nothing else.

So, I only want certain students, from the Student Records table, to be English majors. Right now all students appear in all majors.

[Lupp]

(Spoken aside by someone not being a native speaker of English)
What’s ‘Major’ or an “English major” in this context?

[oakvich]

In the USA a “major” is a student’s area of study in college.

[Ratslinger]

Hello,

Your problem is with keys. Each table must have a unique key to identify records. In Student Records table it is the Student ID field. However in your degrees tables you never specify a unique key - just Student ID.

Each of the degrees tables should start with an auto-increment integer field for the key. The Student ID field in each is used as a link back to the Student Records table thus creating a one-to-many relation for Student Records table to each degrees table. The name of the student in the degrees tables is a duplication of what is already in the Students Records table and totally unnecessary.

You many want to review Chapter 3 - Tables - Relationships between tables for more information (docs located here).

Edit:

Here is a quick sample based on answer & comments. This is only one solution. Depending on needs, there are a vast amount of ways to link data and tables. For the most part macros are not needed. What is truly helpful is knowledge of SQL. This is typically a major tool in using DB’s as it can do many things you think macros would be needed for.

The sample is simple. Three tables. Student Master, Degrees & Degrees Applied.

Student Master contains the all of the students specifics & uses the unique ID. The Degrees table is just a list of available degrees. The name itself is the key since it is unique. The Degrees Applied table ties the other two tables together and allows its own specific information as necessary.

The Degrees Applied table is tied back to the Master via the student ID number and the degree field is a listbox choice of all items in the Degrees table.

Now if you ever want a list of who is majoring in English, this is where SQL is handy. Create a query with all the info wanted and, if desired, display it in a form. Lots of different variations I can think of such having a form with a list box, select the major wanted, press a button and those students with that major appear in a table control.

All these different methods are discussed in the documentation I pointed you to.

Sample: Students.odb

If this answers your question please click on the (upper left area of answer).

[oakvich]

Thanks for taking the time to respond. I should have clarified that I have the keys and relationships set up already. I want to do exactly what you said, I would like the degrees table to copy certain fields from the student records table but only certain students since not every student is going to major in every degree. So John Doe will be and English Major and Jane Smith is a Psychology Major. I’m thinking I may have to create script or macro to trigger this.

[Ratslinger]

@oakvich Based upon comment in original question, I don’t believe you have relationships set correctly since all students are in all majors. Also, as mentioned in my answer, duplication of data should be avoided. Don’t understand why you want info in degree table which already exists in master record. It also occurs to me you really don’t need different tables for different degrees. Just got done with a time consuming task. Will add to my answer shortly with example of my comments.

[juliasmith]

This resolved the problem! Thanks!