Help - newbie

[Sorbz62]

Hi guys,

Newbie here! I am working through Access 2010 in order to write a DB for students on multiple courses. This is what I want to do:

1. Have 4 tables with different type of student. I also want 1 table with all students but cannot work out how the append query works or if it does it automatically. I end up with duplicate data in the all student table and 4 times as many fields.

2. I have a list of courses but I need to be able to relate them to years and then months and link these to Year/ month documentation on the same PC.

3. I want a tabbed form, which has tabs for each student type and each course.

4. Finally I need a form which I presume is a query where I can input either student name or course and prodeuce a report on whichever I select.



I am a bit lost now with relationships and queries. I have the student tables, courses by name etc and basic input forms but am confused with how to get the relationships linked to make my complicated form.

Please help!
Jim

[Sorbz62]

By number 2 above I mean:

I have many courses listed in 1 table. I have nother table for 'Year' and another for 'month'. I'd like to be able to have a form whereby I can select the year and then month by drop-down box and then have a link to the documentation of that specific course. I'd also like to limit the drop-down box to only those months where ther are actual courses.

I hope this makes sense to you.

Ta,
Jim

[rpeare]

you probably want a table setup like this:

tblStudents
StudID (autonumber, unique key)
StudFN
StudLN
StudMI
other student related fields....

tblClasses
ClassID (autonumber, unique key)
ClassName
other class related fields....

tblTerms
TermID (autonumber, unique key)
TermStart (date/time)
TermEnd (date/time)

tblStudentClasses
SCID (autonumber, unique key)
StudID (tblStudents foreign key)
ClassID (tblClasses foreign key)
TermID (tblTerms foreign key)
ClassGrade
Other Student Class related fields....

With this setup you can figure out anything else you want based on date manipulations. Having a table dedicated to year and month is pointless.

[Sorbz62]

Actually I think I've just had an epiphany! My thought process has been that all the forms and queries etc fill in a master table, in my case a master student table with all courses, dates, places, times etc on it. I have just realised that this massively duplicates data in that if I have 20 students on 1 course, I have 20 entries for the same course in the same table. Am I correct in assuming that all I need to do is have the student list and relate that to the course list for example, using a data entry form?

Jim

[Sorbz62]

So.....

I have just started again with your suggestions of tables, PKs, etc and am I correct in relating the students through the junction table (tbl_Student_Cses) to tbl_Cses and tbl_Cse_Details .......

Inotherwords. On the relationships tab, I have got tbl_Students on the left joined as 1-to-many to tbl_Student_Cses and the other 2 tables on the right, linked also to tbl_Student_Cses by 1-to-many.

Hope this makes sense!

Jim

[rpeare]

Right, most people call that a junction table. In most cases of a many to many relationship (many students can take the same class, and many classes can have the same student) you need a junction table to handle the data.

[Sorbz62]

Thanks for the advice. I think I am getting there.

Just another question for you ......

If I do not have a table with all student details in it (ie personal details, courses taken, grades, etc) .... where is the information kept? Surely there must be a master table that has all fields in relating to each student?

I have just created a data entry form but cannot see how to link all the data. I want a form with current students and to enter new student on. That's OK - its bound to the student table. What I can't do is have tabbed subforms for courses for these individuals, ie course 1, course 2 etc. I dont know what to bind the tables to and where the data will be stored.

Jim

[rpeare]

I don't know what your setup is. There are a couple of things you can do 1 is to create another junction table that is terms/classes (may terms can have a single class, and many classes can be in the same term) instead of carrying the termID on the tblStudentClasses table.

If you did it this way your main form could be based on the term, you could add classes to it, and your subform could be the students attending each class, it would probably be the easiest data entry. But again it really depends on what you want and how easy you want the data entry to be for the end user.

Student specific information would be in the student table, where I have 'other student related fields...' you put in whatever fields are necessary for each student record to have in your database.

[Sorbz62]

Again, I am confused. If all my student information is in 1 table, why do I need other tables? I could have the names, course 1, course 2, etc in 1 table and have 1 form to input data but I don't think I'm using the power of Access.

I want to have a form with student details at the top and tabbed sub-forms showing Course 1, Course 2, Course 3 etc underneath. Can I not do all that just from 1 table.

Jim

[rpeare]

Do some reading on the normalization of data. You *can* do it on one table but you *shouldn't* do it because it is going to make it a lot, lot, lot more difficult on you in the long run.

Let's say you have a student for four years. The student takes 4 classes a turn and there are 2 terms per year. You would have to have a table that was 4x4x2 columns wide at the very least to handle that or 24 columns per student. Now, from that, all of those students are not going to take the exact same classes nor are they going to take them in the exact same order. If you build your table the way you are proposing you will have a murderous time trying to find out which students took which classes during which term. If you build with a NORMALIZED table structure this type of operation is very, very simple.

On the surface it appears to be a simple solution, but as you do more programming or try to query your data it makes the complexity of handling your data significantly higher than the easier table design merits.

[Sorbz62]

Do some reading on the normalization of data.

Let's say you have a student for four years. The student takes 4 classes a turn and there are 2 terms per year. You would have to have a table that was 4x4x2 columns wide at the very least to handle that or 24 columns per student. Now, from that, all of those students are not going to take the exact same classes nor are they going to take them in the exact same order. If you build your table the way you are proposing you will have a murderous time trying to find out which students took which classes during which term. If you build with a NORMALIZED table structure this type of operation is very, very merits.

I have read all about normalisation in order to never repeat data and I rebuilt my DB like your suggestion but how do I input data which then tells me which course a particular student has done or which students are on each course etc? Maybe I'm being thick but surely there must be a master table with all students on it?

I mean - how do I search the DB for a particular student and get all his data on one form/ report, including which courses he has done, hyperlinks to reports and such like? I am presuming we are linking tables using the relevant keys but I'm not understanding it yet.

Jim

[rpeare]

I have read all about normalisation in order to never repeat data and I rebuilt my DB like your suggestion but how do I input data which then tells me which course a particular student has done or which students are on each course etc?

If you use the table structure I suggested then this is all done through queries without any big issue.

Maybe I'm being thick but surely there must be a master table with all students on it?

Yes, read my original post about table design, there's a student information table. But classes are kept in a separate table, and the classes a student takes are in yet another, different, table.

I mean - how do I search the DB for a particular student and get all his data on one form/ report, including which courses he has done, hyperlinks to reports and such like? I am presuming we are linking tables using the relevant keys but I'm not understanding it yet.

Not hyperlinks you can build reports that give you information on a single, a collection, or all of your students in a single report. As long as each table has a unique identifier and you enforce the relationship between parent/child table in your forms and subforms it really becomes easy to generate these types of reports.

[Sorbz62]

Thanks for all this so far.

By hyperlinks to reports, I meant to .doc course reports and such like so I can quickly see them.

Ta,
Jim

[Sorbz62]

I have now got the relationships OK.

I am now stuck on designing a form which has the student details at the top with tabs for - Course 1, Course 2, Course 3.

I want to be able to add details of courses for individual students but when I add data, it appears to be the same for EVERY student, ie main form student details reflects the student OK, but if I add a course name in the tab for course 1, then go to the next student, that course name is in his tab ....?

HTF do I sort this out. I may go back to 1 table with students and course details which will duplicate data but easier for me to undrstand. Please help me!

Jim