Joins

[Ande]

I have created a database - a table for each subject e.g. English; Maths; Art
etc. Each table has fields such as the SubjectID, StudentId, Effort,
Attainment, Teacher's comments etc. I also has a table called Students with
fields Id, Name, Surname, Form, Tutor's Comments. The subject tables ar
linked to the Student table via the Student ID as a foreign key. When I made
a query to in clude all students and subjects, I used the outer JOIN #2. As
student sare allowed to choose theri subjects, there will be blank fields for
all students and this is one way of enabling the balnk fields to show.

The problem I am experiencing is the inability to edit or add information in
the fields in the datasheet view of the query. What am i doing wrong??

 

[Rick B]

On thing I spot right off the bat is that your structure is wrong. You
should not create a separate table for each subject. Any time you have more
than one table with identical data structures, you know you have strayed
from database normalization rules.

 

[tina]

forget queries, forms, and everything else for a minute. your tables design
is not normalized. you're putting data (English, Maths, Art, etc) into table
names, which is a big no-no. from your description, sounds like you need a
minimum of three tables, as

tblStudents
StudendID (primary key)
FirstName
LastName
(other fields that describe a student. note that Form and Tutor's Comments
do not describe a student, they describe an instance of the student's
interaction with the curriculum.)

tblSubjects
SubjectID (pk)
SubjectName (English, Maths, Art, etc - one record for each subject)
(other fields that describe a subject.)

tblStudentSubjects
StuSubID (primary key)
StudentID (foreign key from tblStudents)
SubjectID (fk from tblSubjects)
Form (i'm assuming that "Form" is equivalent to "quarter", "semester", or
maybe grade level like "freshman, sophomore, junior, senior")
Effort
Attainment
TeacherComments

i have a feeling that your table setup needs to be expanded further to
appropriately model your process, but without more detailed information i
can't make specific suggestions.

i strongly recommend that you read up on data normalization and table
relationships. this is by far the single most important aspect of database
design, it must be done correctly *before* you begin building queries,
forms, etc - and the time you expend in learning to do it right will be
repaid a thousand-fold in avoiding the many problems and headaches
associated with trying to build workable objects on top of a poorly designed
foundation.

for a list of excellent resources, see
http://www.ltcomputerdesigns.com/JCReferences.html
beginning with the Database Design 101 and Starting Out links.

hth

 

[Ande]

Thank you for the comments. They have been a great help.

ASW

 

[tina]

you're welcome

Thank you for the comments. They have been a great help.

ASW