Ask Your Question
0

Student database

asked 2017-06-30 19:43:58 +0200

oakvich gravatar image

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.

edit retag flag offensive close merge delete

Comments

(Spoken aside by someone not being a native speaker of English)
What's 'Major' or an "English major" in this context?

Lupp gravatar imageLupp ( 2017-06-30 20:36:39 +0200 )edit

In the USA a "major" is a student's area of study in college.

oakvich gravatar imageoakvich ( 2017-07-01 00:53:52 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2017-06-30 20:48:54 +0200

Ratslinger gravatar image

updated 2017-07-01 02:43:16 +0200

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).

edit flag offensive delete link more

Comments

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.

oakvich gravatar imageoakvich ( 2017-07-01 00:52:52 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2017-07-01 02:09:53 +0200 )edit

This resolved the problem! Thanks!

juliasmith gravatar imagejuliasmith ( 2018-09-18 09:02:24 +0200 )edit
0

answered 2019-05-10 15:33:48 +0200

updated 2019-05-10 16:00:18 +0200

Lupp gravatar image

(Deleted; was spam)

edit flag offensive delete link more

Comments

I will delete your complete posts without further notice if you continue to post spam.

Lupp gravatar imageLupp ( 2019-05-10 16:01:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-30 19:43:58 +0200

Seen: 638 times

Last updated: May 10