Student Database for Course Enrollments

[Guest]

I've created a database for all students enrolled in a university science
program and now I'm having great difficulty extracting the information I need
without having to create a lot of queries.

The database must include contact information about every student ever
enrolled in the program along with every course each student has taken and
which semester each course was taken. (Plus a lot more information.) It is
possible that a student was enrolled in more than 1 class during a semester.

Some of the data I need to get is:

1) What are the names of the students enrolled in a given semester? In a
given academic year (3 semesters)? since the beginning of the program (in
2000)?

2) Which students enrolled in biology,and/or physics,and/or ecology, etc.
for a given semester? for a given academic year? since the beginning of the
program? There are 6 courses students must take to complete the program.

3) A combination of 1 +2 above.

At the moment, I just have one large table with all of the information, but
now it seems that I should create more tables. I haven't worked with
relational tables before, but I think that is what I must do. But, I'm not
sure how many tables I need or how to organize the data. Right now I have a
table that has a field for each course given and the student records show
which semester the student took that course.

I don't have a lot of experience working with formulas and none creating
macros, but I'm willing to give anything a try. I've gone through the entire
access help trying to find a solution to my problem, so I've tried out some
of the formulas I've seen there.

Thanks to anyone who can help.

 

[Guest]

Hi Sue,

form what you have described you need 3 tables;

tblStudent
StudentID
StudentName
all other Student only info

tblCourse
CourseID
CourseName <- ecology, biology etc as records, NOT fields
Course Description etc.

tblLinkStudentCourses
LinkID
StudentID
CourseID
DateStarted

use the link table to store the reference to the Student and the Course Name
with the date, then you can join the link table to the other 2 as one-to-many
joins, making drawing together the info you require easy/er

TonyT..

 

[Roger Carlson]

The reason you're having such trouble is because of your database design. On
my website (www.rogersaccesslibrary.com), is a small Access database sample
called "TrainingRegistration.mdb" which does something similar. Perhaps if
you downloaded and studied it, you might get a better idea of how to
construct your own database.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Smartin]

I've created a database for all students enrolled in a university science
program and now I'm having great difficulty extracting the information I need
without having to create a lot of queries.

The database must include contact information about every student ever
enrolled in the program along with every course each student has taken and
which semester each course was taken. (Plus a lot more information.) It is
possible that a student was enrolled in more than 1 class during a semester.

Some of the data I need to get is:

1) What are the names of the students enrolled in a given semester? In a
given academic year (3 semesters)? since the beginning of the program (in
2000)?

2) Which students enrolled in biology,and/or physics,and/or ecology, etc.
for a given semester? for a given academic year? since the beginning of the
program? There are 6 courses students must take to complete the program.

3) A combination of 1 +2 above.

At the moment, I just have one large table with all of the information, but
now it seems that I should create more tables. I haven't worked with
relational tables before, but I think that is what I must do. But, I'm not
sure how many tables I need or how to organize the data. Right now I have a
table that has a field for each course given and the student records show
which semester the student took that course.

I don't have a lot of experience working with formulas and none creating
macros, but I'm willing to give anything a try. I've gone through the entire
access help trying to find a solution to my problem, so I've tried out some
of the formulas I've seen there.

Thanks to anyone who can help.

Hi Sue

In addition to the other great suggestions you might want to look at
this table design I created in response to a similar question:
http://vfdrake.home.comcast.net/files/students.pdf
It has not been proved in the field but it may give you some ideas of
how to normalize your data.

You may not need to know anything about formulas, macros etc., but I do
think you need a thorough understanding of normalization to make your
database work properly.

I strongly suggest you google the topic of normalization and spend some
time learning the ropes. It sounds like you have a fairly complex
situation and normalization is key to effectively modeling this in a
database.

Let us know if we can be of further assistance.

 

[Guest]

The one thing I would add is Do NOT give up.

I have designed a database for my workplace. Before I started I didn't have
a clue about database design and my initial idea was completely wrong, but
once you get a feel for it, you will understand it a lot better. The learning
curve is almost vertical.

The most important rule is that all "like" information should be stored in
the same field (student name, course name, dates etc etc) and each table
should have unique information about a specific aspect of your database.

 

[Guest]

I'd like to thank everyone who answered my question. I've now learned about
normalizing data, which should be helpful in the future.

I am in the process of normalizing my data and breaking my large database
into several smaller ones. However, I don't see how this will make it any
easier to extract the data I need. I'm also afraid I'll have to redo all of
my forms (which took a long time to create) because they were all based on
the original, large, table.

I experimented with the setting up tables similar to the ones in the
suggestions and will do things like have one table for student info, one for
classes, etc. But, after breaking down the data, I'm still not able to get
one query that will let me see all of the students who were enrolled in a
particular semester, along with the name of the class the student was
enrolled in. I think I just have too many variables that I want to search at
one time.

One way I have been able to get at some of this data is to create a yes/no
field for each semester. I also have a table that has a field for each
course and the semster it was taken. In the end, I think I'll still need to
do a bunch of separate queries and then combine and manipulate the data in
Word or Excel, which is what I've been doing. I'm mostly using the data for
merges anyway, and my boss has a Mac and can't use Access, so if she wants an
electronic version, I have to put it into Excel anyway.

So, thanks for all of your suggestions.

Sue

 

[Guest]

Hi again Sue,

I'd like to thank everyone who answered my question. I've now learned about
normalizing data, which should be helpful in the future.

I am in the process of normalizing my data and breaking my large database
into several smaller ones. However, I don't see how this will make it any
easier to extract the data I need. I'm also afraid I'll have to redo all of
my forms (which took a long time to create) because they were all based on
the original, large, table.

If your forms are bound to the original main table, it shouldn't be too much
work too change the control source for each to a query or two.

I experimented with the setting up tables similar to the ones in the
suggestions and will do things like have one table for student info, one for
classes, etc. But, after breaking down the data, I'm still not able to get
one query that will let me see all of the students who were enrolled in a
particular semester, along with the name of the class the student was
enrolled in. I think I just have too many variables that I want to search at
one time.

If you get the table designs right, it should be fairly straight forward to
produce the queries you need, although sometimes you will need to create
basic queries and then use these as the source for other queries to resolve
some inner joins.

At the end of the day, Access is designed for tables to store data, queries
to combine distributed data, forms to enter and display and Reports to print
information, with the added benefit that you can manipulate your data and
export it to word or excel as you suggest, but without having to do anything
other than send the end result off!

One way I have been able to get at some of this data is to create a yes/no
field for each semester. I also have a table that has a field for each
course and the semster it was taken. In the end, I think I'll still need to
do a bunch of separate queries and then combine and manipulate the data in
Word or Excel, which is what I've been doing. I'm mostly using the data for
merges anyway, and my boss has a Mac and can't use Access, so if she wants an
electronic version, I have to put it into Excel anyway.

You can often get round these yes/no fields by checking on other data, eg if
not isnull Date course started will tell you the same information as 'did the
student start a course'. Thinking of ways around little things like that can
make all the difference to how efficient your database becomes.

So, thanks for all of your suggestions.

Good luck and enjoy the proccess!!

TonyT..

 

[Guest]

Thanks. I'll keep trying.

Hi again Sue,


If your forms are bound to the original main table, it shouldn't be too much
work too change the control source for each to a query or two.


If you get the table designs right, it should be fairly straight forward to
produce the queries you need, although sometimes you will need to create
basic queries and then use these as the source for other queries to resolve
some inner joins.

At the end of the day, Access is designed for tables to store data, queries
to combine distributed data, forms to enter and display and Reports to print
information, with the added benefit that you can manipulate your data and
export it to word or excel as you suggest, but without having to do anything
other than send the end result off!
You can often get round these yes/no fields by checking on other data, eg if
not isnull Date course started will tell you the same information as 'did the
student start a course'. Thinking of ways around little things like that can
make all the difference to how efficient your database becomes.


Good luck and enjoy the proccess!!

TonyT..