Question Simple Walkthrough of assigning student to course

[neilq5]

I have all my student info table, what would be the next step to enrolling the students onto a course.
i've set up a course table of (Course_ID, Course Name)

note: before enrolling on a course most students (99%) take a trial during there school year before enrolling
so i've set up a trial table
there's numerous types of trials that happens a view times a year so my table currently looks like this
(Trial_ID, type of link, day of trial, group, session)

the type of link, is how the trial is determined (same as trial name)
Day of trial is determined by which trial it is
the group, is the class group as there can only be 10 students per group so there could be up to 4 groups in some trials happening on the same day but doing different subjects
the Session is which trial is occuring as there can be up to 4 a year and dates are not decided until later on so the session list would be named simply 1,2,3,4.

Can someone give me a simple walkthrough of setting this up?

 

[jdraw]

Do not have field names with spaces or special characters.
Use only alpha, numbers and underscore.

note: before enrolling on a course most students (99%) during there school year before enrolling

What does this mean?

I recommend you write a 2-3 line description of the "THINGS" you are dealing with. This will help your understanding of the data. You really need to identify business rules to identify How the THINGS are related.

Here are a few examples of business rules to help you get a "feel" for business rules.
http://www.databaseanswers.org/data_models/student_exams/facts.htm
http://www.databaseanswers.org/data_models/student_internships/facts.htm


Here is a general approach to database design
http://www.databaseanswers.org/approach2db_design.htm

 

[neilq5]

apologies, i made a spelling mistake and forgot to replace the word

it should be
before enrolling on a course most students (99%) take a trial during there school year before enrolling

here's the main business rules

A student may enrol on one and only one course at a time
A student may change course later in the year
A student may enrol on one and only one trial
A tutor may teach one or more courses
A course may be taught be one or more tutors
A student can choose one or more subjects

 

[jdraw]

So the "things of interest" would be

Student
Course
Trial
Tutor
Subject

What is a Course compared to a Subject? Please give an example.

I would suggest (for clarity) Students enroll in Courses;
Students participate in Trials. [Just trying to remove any ambiguity with enroll]

You may wish to research Junction Table. You will need to use this concept to resolve M:M relationships.

 

[neilq5]

Each course has a list of subjects that change every year,
So students gets a list of choices of subjects to take on each course.

so i could have a Course called Network
and for Friday period 1, there could be a choice of 6 Subjects the student can choose from at that time

 

[jdraw]

You have added more THINGS to be considered.

SchoolYear
ClassSchedule
ClassPeriod
possibly StudentChoice??

You may get some ideas from Roger Carlson's Class Info set up here
http://www.rogersaccesslibrary.com/Tutorials/ClassInformation.zip

For Course Network what are the related Subjects?

 

[neilq5]

The subjects are still to be determined by the college.
It may be a few weeks before they have them so my plan was to create a Subject field (wherever that may be appropriate)
and leave it blank to be entered later at a later date by one of the tutors as I will be leaving the college as of August 14th.

I'll have a look over the link you provided and see it will be of use.

 

[neilq5]

Should i perhaps list the Trials together with the Courses and have an Enrollment table?

 

[jdraw]

Where exactly are you in the project at this point?
Do you have a working data model?
Do you have test data?

What exactly is your deliverable for Aug 14?
Who will receive it? Then what?

 

[jdraw]

Should i perhaps list the Trials together with the Courses and have an Enrollment table?

No, don't start there. Write a 2-3 line description of Course and same for Trial.
See if those descriptions do not highlight 2 separate things.
Seriously, write the descriptions. You'll be surprised why some things are different (should be in different tables).

Read this Database principles article
http://forums.aspfree.com/attachment.php?attachmentid=4712

 

[neilq5]

Right now in the project I have Forms set up to record student information.

I also have queries to instantly find emergency contacts, etc.

my forms and queries are working good with the test data i have entered.


The purpose of the database is to act as a replacement to that of the database the college is already using as currently they are using a several different databases without the use of forms and information is jumbled all over the place with copies of tables, reports and queries.


By August 14th i'm hoping to get a working database to replicate what the other database used to be able to do and the tutors will then test the database for 4 weeks and make a list of improvements to be made

 

[jdraw]

Do you have a data model?
Do you have good and bad test data? Don't only test with valid data.

Can you show a jpg of your tables and relationships? You must have something if you have Forms and queries established.

What happens to existing data in the older databases/systems? Does it have any historical significance or will it be discarded?

Who will receive the database? Then what?
Seems like Aug 14 is the deadline-- you'll leave.
What if the system is not complete? What about the changes that are discovered?

Do you have regular meetings with those who have tasked you to do this?

 

[neilq5]

Historical data is usually discarded after the academic year.
All Test data so far has been good data.

I will get a jpg of the relationships asap

I work from in the college, with staff members at hand some days to ask questions. All staff members return to the college full time next week so they will be at hand all the time.
I will be moving house August 15th and will be unavailable for several weeks after that.
So August 14th is not an official deadline - just a preferred completion date.
Any problems that are discovered over the 4 week trial period will result in my returning to the college to fix. Same goes for if the system is not complete

 

[neilq5]

the other tables/relationships I didnt display is more student information i've left in other tables thats all relating to the StudentID.

It includes:
Emergency Contacts
General enquiry contact
SchoolInfo
Support Needs
etc.

 

[jdraw]

I strongly recommend you get rid of all names with embedded spaces.

I think your attached PNG is not correct (my suspicion only).
I think you are missing an Entity -- Class(or similar name) where a Class or Class session is identified by a specific Tutor, and Subject at a specific time in a specific location.

Seems a Course is divided into Subjects, and Subjects are further divided into specific Classes(Sessions). A Session with Student(s), Tutor, Time, Date and Location.

Your table tblCourses seems too high a level (should be Class/Session in my view). If Course was Network, from an earlier post, I don't think it would be related to Student, Tutor directly.
see http://www.databaseanswers.org/data_models/student_rosters/index.htm

What documentation do you have to hand-over to those who will be running the data base/testing etc while you are away?

 

[neilq5]

i'll be doing a demonstration to the main tutors that will be doing the database with a troubleshooting guide for any error messages they may receive (entering text into numeric value field)

the link you provided looks as if it will be of help.

If it will make it easier for you i could send you a copy of the db for you to see?

I'm about to leave for home so it will take a couple of hours.

and i've found a problem with one of my forms you may be able to help fix

 

[jdraw]

I have acc2003 and can read/use mdb files only.
You can post accdb file, but I won't be able to use it.

 

[neilq5]

Hi jdraw apologies for the delayed reponse i was having some internet troubles in the house last night.

The problem i was having with my forms was:
I have my main form frmPersonalInfo
that has buttons linking to my other info forms such as frmEmergencyContacts.
to link them together i used this code on my other forms:

Private Sub Form_Current()
Me.Studid = Forms!frmPersonalInfo.StudentID
End Sub

Which made the PK field in my frmPersonalInfo match the one in my frmEmergencyContacts.
this code works okay and i add this code to my frmEmergencyContacts

Private Sub Save_Click()
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
End Sub

My problem is that when i had a new record in frmPersonalInfo and open the frmEmergencyContacts
the previous entry opens but with the ID for the current new record, which edits the entry from the previous record.

I had it working okay in a previous prototype but im not sure what i did differently this time round.

I can post my database in mdb format if you wish. I don't know if there will be any compatibility errors

 

[alcapps]

i was looking at your diagram. I would keep student, Course, Tutor, but I would think of it a little different possibly. take out the course tutor and student ID and keep course about the course. keep the tutor the same trying adding another table Tutor2Student. Put a ID, CourseID, StudentID, TutorID in there. Fix the course table by removing the Tutor and student ID. Create another table for Classes have a ID and put CourseID, StudentID in it with Dates and grade info for it. That should do you better.
I hope this makes sense.
so you will have a student table to hold student info only. a Courses table to hold courses or classes that you offer. Tutor table for instructors or tutors. A tutor2student table so you can show tutor and student combo's. then finally a classes table to show the course and student info that a student would take. Here you could have grades or a separate table for that as well. hope this helps..

you can use the courses table to indicate weather or not the course is a trial or not make an isTrial Boolean field, default to false.

 

[neilq5]

Whats the best way to handle enrolments?

I've tried a few things but can't figure out the best way to go about it?

I will enrolments for the trial and the course.

For the course,
I'll need something to show the status of the students that have been offered a place.
i.e
Letter Sent
Not Accepting
Place Accepted

I'll also need a yes/no for if the place offer is a Conditional.

I'm really stuck with this so ANY input will be appreciated!