Auto-sort from one table to sub-table

[siedne]

I am helping my professor with her student database. I want to split the students up into pre-certified, certified and completed majors. I have one table now of all students.

Can access automatically detect which student should go in which table if certain fields in the all student table are filled out in a certain way?

Ie. If I enter information on a new student in the all student database, and leave the certification date field blank, is there some way access can detect this, dublicate the data and group this person in the pre-certified table?

[boblarson]

You should not be putting them into those three tables. You should continue to use ONE table and you would have another table which is going to be a many table which you can assign students to one or more of those (because a student can have a completed major but still go on to do another one).

[siedne]

Thanks for the quick response. How do I make a many table?

[boblarson]

Thanks for the quick response. How do I make a many table?

Well, the first thing I would suggest is to go read this about normalization:
http://support.microsoft.com/kb/283878
It will help you to understand what I am talking about. Normalized data is the key to most smooth running databases and also for making them the most easily updated with new information and then getting the data back out in a meaningful way.

[siedne]

Ok, so now are you suggesting I make a one-to-many table with All Students as the main table?

[boblarson]

Ok, so now are you suggesting I make a one-to-many table with All Students as the main table?

That is essentially a correct statement.

[siedne]

You should not be putting them into those three tables. You should continue to use ONE table and you would have another table which is going to be a many table which you can assign students to one or more of those (because a student can have a completed major but still go on to do another one).

Ok so I've created one-to-many relationships for the database, but how do I assingn students to either pre-, certified or completed?

[boblarson]

Okay, let's take a step back here. Your structure is still not correct. You have a table for each subject and you should not. Your structure should look something like this: