general problems

[Gruung]

Hi,
I am creating a database for the 6th form in my school. The timetable is split up in to blocks, so for example in block A there are 5 different subjects and in block B there are 9. I have one table for all the students,where one feild is a lookup for what year they are in (12 or 13). I also have a table for the blocks so i have 12a,12b...13a,13b where the avalible subjects for that block are displayed. In my students table i have Block a, block b, block c... I want a lookup for these so that if the year is 12 then it will display 12a under the block A feild in the students table and 13a if the year is 13. Currently I have this expression in my query IIf([Students]![Year]=12,[Block2]![12a],[Block2]![13a]) but when i run it in the table it asks me the year. Why does it not just recognise the year straight away.

My second problem, is that there are reports that create class list. The reports ask you the year and the subject, currently I have a seperate report for each block. The queries are the same aparts from the fact that they select the subject from block b rather than block a. Is there a way that I can make it so that i can make it a lookup asking her what block she wants to use?

thanks in advance

gruung

 

[Mile-O]

You almost make it sound as if you have recreated a spreadsheet in a database which is a BIG mistake.

You'll need one table for students.
You'll need one table for classes.
You'll need one table for objects.

You should, to be honest, need one table for each "object" your database uses.

Search this forum for hints and information about "normalisation/normalization" or - in layman's terms - how to produce a proper relation database.

 

[Gruung]

thanks, I understand what you mean but am not sure how to go about it, I think i might have explained myself badly. I am going to attach a screen shot of my relationships, if you dont mind can you tell me what you think is wrong and how i can change it. Thanks

 

[Mile-O]

Prepare yourself....

Nearly all of your tables contain repeating groups which goes against database design.

A database should grown downwards and not stretch wider and wider. So, if you have something that goes Field1, Field2, Field3, Field...n then that is a good indication that it deserves a table of its own

A table should contain information pertaining to one thing. Everything in the table should be dependant upon the primary key.

An school example, as you have posted, would have student details:

i.e.

StudentID (autonumber)
StudentForename (text)
StudentSurname (text)
Tutor (text)
English (Yes/No)
French (Yes/No)
German (Yes/No)
Maths (Yes/No)
....
and so on, and so forth...

Now, looking at those fields we have identified that our "object" is a student and therefore everything in this table must be dependant upon the student. The StudentID will be our primary key. So, obviously the students' names will be dependant on the StudentID but the Tutor isn't. The tutor does not depend on that particular student but can apply to many other students and therefore becomes another "object" to factor into your database.

So we make a a table for Tutors:

TutorID (autonumber)
TutorForename (text)
TutorSurname (text)

This stores all our tutors. But, going back the student table we still want to identify who our students' tutors are. So, we create a foreign key.

StudentID (autonumber)
StudentForename (text)
StudentSurname (text)
TutorID (number)
English (Yes/No)
French (Yes/No)
German (Yes/No)
Maths (Yes/No)
....
and so on, and so forth...

Note now that the Tutor field has been removed and replaced with the numerical field TutorID. This stores the autonumber value relating to a tutor held in the tutors table. The relationship created would be a one-to-many as one tutor can have many students. (I'll discuss many-to-many i.e. many students can have many tutors and vice versa later.)

The reason why this is better, for one, is to imagine a female teacher called Mrs Sexy. She has a number of students and then one day she gets married and becomes Mrs Frump. In the first case (Tutor field) you would be required to manually update every record in the Tutor field to ensure all trace of Mrs Sexy has gone. With the second example, all that's needed is to change the surname in the Tutors table and, because the ID key is used in the other table, all the records in the student table are immediately updated.

Now, to the next problem. The tutors are sorted out but there is a repeating group: English, French, German, Maths.

The first thing this says is: subjects are deserving of a table of their own. In other words: Subjects are an object within our database design.

So, we create a new table:

SubjectID (autonumber)
Subject (text)

But we can't do the same thing this time as out students can have many subjects and our subjects can be taken by many students. Now we have a many-to-many relationship. Access doesn't immediately support this type of join so we have to simulate it with another table. A junction table, if you will.

This table will have the fields:

StudentID (number)
SubjectID (number)

It contains foreign keys to the student table and the subject table respectvely. These two fields (even though they are foreign keys) can also be selected to be the primary key of this table. And they should. This will ensure that over the two fields there are no duplicates meaning that John Smith can only do English once.

So, we relate these foreign keys to their respective tables. Thus we have two one-to-many joins that simulate a many-to-many relationship.

So, we have four tables from the initial one. We can declare our database "normalised". Having your database normalised reduces the risk of problems in design and future maintenance. Afterall, you don't want to have to come back and add a new subject in to a denormalised table when the school decides to offer a course in Latin, do you? That would mean having to rebuld queries, forms, reports, and maybe even touching up code. Having it all normalised means you'd just add Latin into the Subjects table and that's it!

A database will work for most people if it is normalised to Third Normal Form.

The forms are:

• 1st Normal Form (1NF): The purpose of this is to ensure that repeating groups (or duplicate columns) are removed from the same table and that each row of data is identified by a unique column (primary key);
• 2nd Normal Form (2NF): This Normal Form is used to eliminate functional dependencies on partial key fields from your table (think of the TutorID example above) and placing these functional dependencies in a new table;
• 3rd Normal Form: (3NF): The idea of this is to eliminate functional dependencies on non-key fields (storing calculated values) which basically means we eliminate any calculable values. If we have the quantity and the price then we don't need to store the total cost as it is calculable anytime. And if we did store the total cost the margin for error increases as we could easily change the price field but the total cost wouldn't change without intervention.

If a database is in 2nd Normal Form then it is in 1st Normal Form. If a database is in 3rd Normal Form then it is in 2nd and 1st Normal Form.

So, now to your problem:

You can probably see now that your database is in complete violation of 1st Normal Form.

I don't fully know what the fields in your tables signify but the only table that looks semi-okay is the students table although details such as Block1, Block 2 (think 1NF) will cause problems.

It would seem that you have a few many-to-many relationships to simulate.

A tutors table will be necessary.
A subjects table definitely.

I'll let you think about it more now that you should have a better understanding and then you can post back with more questions.

Good luck.

 

[Pat Hartman]

The classes that a students attend should not be stored in the student table. They should be stored in their own table since class has a many-to-many relationship with student. Also, Tutors are usually specific to a subject and so a student may have several tutors, one for each class. Therefore, the TutorID belongs in the classStudent relation table.

 

[Gruung]

thanks

Thank you soooo much. U helped a lot. However I have one question you said that in my students table i should have english (yes/no) frnech(yes/no). The problem with this is that there are over 25 subjects that they can do, but they can only choose 5 of them, one in each block. Surly this is not the best solution, my block a, block b are lookups for subects so isnt it better to do it this way and have 5 feilds rather than 25? The grades table is also a problem. I need a way to store grades for GCSE and AS level, but if I do it so that there is a feild for each subject then with GCSE and AS subjects i would have over 40 feilds. However the way it is now, subject 1,grade 1...subject 2, grade 2.

thank you

 

[Mile-O]

Re: thanks

Thank you soooo much. U helped a lot. However I have one question you said that in my students table i should have english (yes/no) frnech(yes/no).

No, I was starting from a common design problem and working into a normalised table structure. Taking a repeating group such as Yes/No fields that all relate to one thing and breaking them into identifiable objects.

The problem with this is that there are over 25 subjects that they can do, but they can only choose 5 of them, one in each block. Surly this is not the best solution, my block a, block b are lookups for subects so isnt it better to do it this way and have 5 feilds rather than 25?

So, we introduce two new tables:

tblStudentsToSubjects
tblLevels

tblLevels has the fields:

LevelID (autonumber)
Level (text: the level being GCSE or AS, etc)

tblStudentsToSubjects has the fields:

StudentID
SubjectID
LevelID
Grade

The two fields in bold are the primary key and are related to the relevant tables - tblStudents and tblSubjects, respectively.

LevelID is a foreign key that describes what level the student is doing this subject at.

Now, as for limiting the choices to five subjects - that's something that would be done on a form.

On a form, when adding a new subject for a student you can easily use the BeforeUpdate() event to check that the limit of five has not already been reached. That, however, can come later. It's always best to get the tables designed first Then, the queries. And then the forms.

 

[Gruung]

Re: Re: thanks

Is this any better?

 

[Pat Hartman]

It still has lots of problems.
1. Year is a poor choice for a column name since it is the name of a function. This will cause problems with VBA. It may in some cases have trouble determining whether you are referring to a field of your own or a function. Date and Name are other common problem names. If you have any doubts, look up reserved words in this forum's search feature qualified by my name to see links to kb articles with SQL and VBA reserved words.
2. Tutor is still associated with student. I think it belongs in the relation table - StudentsToSubjects.
3. JunctionTable is extraneous. StudentsToSubjects is the junction table and it includes data relevant to the junction of the two related tables.
4. The students table should really be a person table. That way you can use it to store tutor and teacher info also. You can use a role table which will allow any person to be a student, teacher, and/or tutor all at the same time which could happen in a school of higer education.
5. BlueSlips, GoldSlips, Latenesses should be stored discretely by date so they can easily be audited. The problem with the way you have them is there is no period association.
6. You have a Year field in the student record. This actually belongs outside the student table so that you can have data for multiple years in your database. Then all the other tables would link to the studentYear table rather than directly to the student table.
7. You never want to have multiples of the same field in a table. Since you can have more than one note for a student, notes belong in a separate table so you can keep track of who made them and when.
8. It is poor practice to use embedded spaces or special characters in your column and table names. They will become a problem when you start creating event procedures or working with VBA.

Don't get frustrated, database design is difficult to grasp. Keep working at it. A well designed schema will make the development of the rest of your app infinitely easier. It will also reduce the life time maintenance cost of the db.