JOINS

A

Ande

I am a new user and have recently used the wizard to create a small database
of students and subjects. There are over 12 subjects and each subject is
contained in a table. the subject tables are linked to the student able via
the ReportID in the Report table and the ReportID as a foreign key in each of
the subject tables. I tried to make a query of all the students and all the
available subjects and used the 2nd outerJOIN tto enable all the student
records to show with blanks in some of the subjects. The SQL view of the
query is below. Why can't I edit the fields? Why is it read only?

SELECT tblReport.Name, tblReport.Surname, tblReport.Form, tblReport.[Form
Tutor], tblReport.[Form Tutors Comment], tblReport.[Head of Year],
tblReport.[Head of Year Comment], tblReport.[Boarding Mistress],
tblReport.[Boarding Mistress Comment], tblReport.[Boarding Conduct],
tblReport.Category, tbl6FMaths.Block AS tbl6FMaths_Block,
tbl6FMaths.FMathsTeacher, tbl6FMaths.[FMaths Effort], tbl6FMaths.FMathsAttm,
tbl6FMaths.FMathsComments, tbl6FMech.Block AS tbl6FMech_Block,
tbl6FMech.FMechTeacher, tbl6FMech.[FMech Effort], tbl6FMech.FMechAttm,
tbl6FMech.FMechComments, tbl6Mech.Block AS tbl6Mech_Block,
tbl6Mech.MechTeacher, tbl6Mech.[Mech Effort], tbl6Mech.MechAttm,
tbl6Mech.MechComments, tblADArt.Block AS tblADArt_Block, tblADArt.[A&DGroup],
tblADArt.ARTTeacher, tblADArt.DesignTeacher, tblADArt.ARTEffort,
tblADArt.ARTAttm, tblADArt.ARTComment, tblADArt.DesignEffort,
tblADArt.DesignAtm, tblADArt.DesignComment, tblBoardingy13.[Boarding
Mistress], tblBoardingy13.[Boarding Mistress Comment],
tblBoardingy13.[Boarding Conduct], [tblBusiness Studies].Block AS
[tblBusiness Studies_Block], [tblBusiness Studies].Teacher AS [tblBusiness
Studies_Teacher], [tblBusiness Studies].Effort AS [tblBusiness
Studies_Effort], [tblBusiness Studies].Attainment AS [tblBusiness
Studies_Attainment], [tblBusiness Studies].Comment AS [tblBusiness
Studies_Comment], tblChemY13.[Chemistry Set], tblChemY13.ChemTeacher1,
tblChemY13.ChemTeacher2, tblChemY13.ChemEffort1, tblChemY13.ChemAttm1,
tblChemY13.ChemComment1, tblChemY13.ChemEffort2, tblChemY13.ChemAttm2,
tblChemY13.ChemComment2, tblEconomics.Block AS tblEconomics_Block,
tblEconomics.Teacher AS tblEconomics_Teacher, tblEconomics.Effort AS
tblEconomics_Effort, tblEconomics.Attainment AS tblEconomics_Attainment,
tblEconomics.Comment AS tblEconomics_Comment, tblEnglishY13.Block AS
tblEnglishY13_Block, tblEnglishY13.EngTeacher1, tblEnglishY13.EngTeacher2,
tblEnglishY13.EngEffort1, tblEnglishY13.EngEffort2, tblEnglishY13.EngAttm1,
tblEnglishY13.EngAttm2, tblEnglishY13.EngComment1, tblEnglishY13.EngComment2,
tblFoodTec.Block AS tblFoodTec_Block, tblFoodTec.FoodTecTeacher,
tblFoodTec.FoodTecEffort, tblFoodTec.FoodTecAttm, tblFoodTec.FoodTecComment,
tblFrenchY13.FrenSet, tblFrenchY13.BLOCK AS tblFrenchY13_BLOCK,
tblFrenchY13.FrenTeacher1, tblFrenchY13.FrenTeacher2,
tblFrenchY13.FrenEffort1, tblFrenchY13.FrenAttm1, tblFrenchY13.FrenComments1,
tblFrenchY13.FrenEffort2, tblFrenchY13.FrenAttm2, tblFrenchY13.FrenComments2,
tblGeography.BLOCK AS tblGeography_BLOCK, tblGeography.[Geography Teacher],
tblGeography.GeographyEffort1, tblGeography.GeogaphyAttm1,
tblGeography.[Geography Comment], tblGermanY13.Block AS tblGermanY13_Block,
tblGermanY13.[German Teacher1], tblGermanY13.[German Teacher 2],
tblGermanY13.MFLEffort AS tblGermanY13_MFLEffort, tblGermanY13.MFLAttm AS
tblGermanY13_MFLAttm, tblGermanY13.MFLComment AS tblGermanY13_MFLComment,
tblGermanY13.MFLEffort2, tblGermanY13.MFLAttm2, tblGermanY13.MFLComment2,
tblHistory.[History Set], tblHistory.HistoryTeacher1,
tblHistory.HistoryTeacher2, tblHistory.HistoryEffort1,
tblHistory.HistoryAttm1, tblHistory.HistoryComment1,
tblHistory.HistoryEffort, tblHistory.HistoryAttm, tblHistory.HistoryComment2,
tblLS.Form, tblLS.LSTeacher, tblLS.LSEffort, tblLS.LSAttm, tblLS.LSComment,
tblMusicY13.Block AS tblMusicY13_Block, tblMusicY13.MusicTeacher,
tblMusicY13.MusicTeacher2, tblMusicY13.MusicEffort, tblMusicY13.MusicAttm,
tblMusicY13.MusicComment, tblMusicY13.MusicEffort2, tblMusicY13.MusicAttm2,
tblMusicY13.MusicComment2, tblPhysicsY13.PhysTeacher1,
tblPhysicsY13.PhysTeacher2, tblPhysicsY13.PhysEffort1,
tblPhysicsY13.PhysAttm1, tblPhysicsY13.PhysComment1,
tblPhysicsY13.PhysEffort2, tblPhysicsY13.PhysAttm2,
tblPhysicsY13.PhysComment2, tblPhysicsY13.Block AS tblPhysicsY13_Block,
TBLRE.BLOCK AS TBLRE_BLOCK, TBLRE.[RE Teacher], TBLRE.REEffort1,
TBLRE.REAttm1, TBLRE.[RE Comment], tblSpanish13.MFLSet,
tblSpanish13.MFLTeacher1, tblSpanish13.MFLEffort AS tblSpanish13_MFLEffort,
tblSpanish13.MFLAttm AS tblSpanish13_MFLAttm, tblSpanish13.MFLComment AS
tblSpanish13_MFLComment
FROM ((((((((((((((((((tblReport LEFT JOIN tbl6FMaths ON tblReport.ReportID
= tbl6FMaths.ReportID) LEFT JOIN tbl6FMech ON tblReport.ReportID =
tbl6FMech.ReportID) LEFT JOIN tbl6Mech ON tblReport.ReportID =
tbl6Mech.ReportID) LEFT JOIN tblADArt ON tblReport.ReportID =
tblADArt.ReportID) LEFT JOIN tblBoardingy13 ON tblReport.ReportID =
tblBoardingy13.ReportID) LEFT JOIN [tblBusiness Studies] ON
tblReport.ReportID = [tblBusiness Studies].ReportID) LEFT JOIN tblChemY13 ON
tblReport.ReportID = tblChemY13.ReportID) LEFT JOIN tblEconomics ON
tblReport.ReportID = tblEconomics.ReportID) LEFT JOIN tblEnglishY13 ON
tblReport.ReportID = tblEnglishY13.ReportID) LEFT JOIN tblFoodTec ON
tblReport.ReportID = tblFoodTec.ReportID) LEFT JOIN tblFrenchY13 ON
tblReport.ReportID = tblFrenchY13.ReportID) LEFT JOIN tblGeography ON
tblReport.ReportID = tblGeography.ReportID) LEFT JOIN tblGermanY13 ON
tblReport.ReportID = tblGermanY13.ReportID) LEFT JOIN tblHistory ON
tblReport.ReportID = tblHistory.ReportID) LEFT JOIN tblLS ON
tblReport.ReportID = tblLS.ReportID) LEFT JOIN tblMusicY13 ON
tblReport.ReportID = tblMusicY13.ReportID) LEFT JOIN tblPhysicsY13 ON
tblReport.ReportID = tblPhysicsY13.ReportID) LEFT JOIN TBLRE ON
tblReport.ReportID = TBLRE.ReportID) LEFT JOIN tblSpanish13 ON
tblReport.ReportID = tblSpanish13.ReportID;
 
L

Lynn Trapp

Hold your horses there, Ande. Access has difficulty creating an updateable
query with more than 2 tables and, if my count was right, you have 19 join
statements in your query. You are making Access's head swim. You need to
start over and normalize your database. Put all things that are of the same
type (subjects, students, etc.) in individual tables. Use junction tables to
relate those things together. You might have something like this.

tblStudents
StudentID
--Other fields specific to students

tblSubjects
SubjectID
--Other fields specific to Subjects

tblStudentSubjects
StudentID
SubjectID

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Ande said:
I am a new user and have recently used the wizard to create a small
database
of students and subjects. There are over 12 subjects and each subject is
contained in a table. the subject tables are linked to the student able
via
the ReportID in the Report table and the ReportID as a foreign key in each
of
the subject tables. I tried to make a query of all the students and all
the
available subjects and used the 2nd outerJOIN tto enable all the student
records to show with blanks in some of the subjects. The SQL view of the
query is below. Why can't I edit the fields? Why is it read only?

SELECT tblReport.Name, tblReport.Surname, tblReport.Form, tblReport.[Form
Tutor], tblReport.[Form Tutors Comment], tblReport.[Head of Year],
tblReport.[Head of Year Comment], tblReport.[Boarding Mistress],
tblReport.[Boarding Mistress Comment], tblReport.[Boarding Conduct],
tblReport.Category, tbl6FMaths.Block AS tbl6FMaths_Block,
tbl6FMaths.FMathsTeacher, tbl6FMaths.[FMaths Effort],
tbl6FMaths.FMathsAttm,
tbl6FMaths.FMathsComments, tbl6FMech.Block AS tbl6FMech_Block,
tbl6FMech.FMechTeacher, tbl6FMech.[FMech Effort], tbl6FMech.FMechAttm,
tbl6FMech.FMechComments, tbl6Mech.Block AS tbl6Mech_Block,
tbl6Mech.MechTeacher, tbl6Mech.[Mech Effort], tbl6Mech.MechAttm,
tbl6Mech.MechComments, tblADArt.Block AS tblADArt_Block,
tblADArt.[A&DGroup],
tblADArt.ARTTeacher, tblADArt.DesignTeacher, tblADArt.ARTEffort,
tblADArt.ARTAttm, tblADArt.ARTComment, tblADArt.DesignEffort,
tblADArt.DesignAtm, tblADArt.DesignComment, tblBoardingy13.[Boarding
Mistress], tblBoardingy13.[Boarding Mistress Comment],
tblBoardingy13.[Boarding Conduct], [tblBusiness Studies].Block AS
[tblBusiness Studies_Block], [tblBusiness Studies].Teacher AS [tblBusiness
Studies_Teacher], [tblBusiness Studies].Effort AS [tblBusiness
Studies_Effort], [tblBusiness Studies].Attainment AS [tblBusiness
Studies_Attainment], [tblBusiness Studies].Comment AS [tblBusiness
Studies_Comment], tblChemY13.[Chemistry Set], tblChemY13.ChemTeacher1,
tblChemY13.ChemTeacher2, tblChemY13.ChemEffort1, tblChemY13.ChemAttm1,
tblChemY13.ChemComment1, tblChemY13.ChemEffort2, tblChemY13.ChemAttm2,
tblChemY13.ChemComment2, tblEconomics.Block AS tblEconomics_Block,
tblEconomics.Teacher AS tblEconomics_Teacher, tblEconomics.Effort AS
tblEconomics_Effort, tblEconomics.Attainment AS tblEconomics_Attainment,
tblEconomics.Comment AS tblEconomics_Comment, tblEnglishY13.Block AS
tblEnglishY13_Block, tblEnglishY13.EngTeacher1, tblEnglishY13.EngTeacher2,
tblEnglishY13.EngEffort1, tblEnglishY13.EngEffort2,
tblEnglishY13.EngAttm1,
tblEnglishY13.EngAttm2, tblEnglishY13.EngComment1,
tblEnglishY13.EngComment2,
tblFoodTec.Block AS tblFoodTec_Block, tblFoodTec.FoodTecTeacher,
tblFoodTec.FoodTecEffort, tblFoodTec.FoodTecAttm,
tblFoodTec.FoodTecComment,
tblFrenchY13.FrenSet, tblFrenchY13.BLOCK AS tblFrenchY13_BLOCK,
tblFrenchY13.FrenTeacher1, tblFrenchY13.FrenTeacher2,
tblFrenchY13.FrenEffort1, tblFrenchY13.FrenAttm1,
tblFrenchY13.FrenComments1,
tblFrenchY13.FrenEffort2, tblFrenchY13.FrenAttm2,
tblFrenchY13.FrenComments2,
tblGeography.BLOCK AS tblGeography_BLOCK, tblGeography.[Geography
Teacher],
tblGeography.GeographyEffort1, tblGeography.GeogaphyAttm1,
tblGeography.[Geography Comment], tblGermanY13.Block AS
tblGermanY13_Block,
tblGermanY13.[German Teacher1], tblGermanY13.[German Teacher 2],
tblGermanY13.MFLEffort AS tblGermanY13_MFLEffort, tblGermanY13.MFLAttm AS
tblGermanY13_MFLAttm, tblGermanY13.MFLComment AS tblGermanY13_MFLComment,
tblGermanY13.MFLEffort2, tblGermanY13.MFLAttm2, tblGermanY13.MFLComment2,
tblHistory.[History Set], tblHistory.HistoryTeacher1,
tblHistory.HistoryTeacher2, tblHistory.HistoryEffort1,
tblHistory.HistoryAttm1, tblHistory.HistoryComment1,
tblHistory.HistoryEffort, tblHistory.HistoryAttm,
tblHistory.HistoryComment2,
tblLS.Form, tblLS.LSTeacher, tblLS.LSEffort, tblLS.LSAttm,
tblLS.LSComment,
tblMusicY13.Block AS tblMusicY13_Block, tblMusicY13.MusicTeacher,
tblMusicY13.MusicTeacher2, tblMusicY13.MusicEffort, tblMusicY13.MusicAttm,
tblMusicY13.MusicComment, tblMusicY13.MusicEffort2,
tblMusicY13.MusicAttm2,
tblMusicY13.MusicComment2, tblPhysicsY13.PhysTeacher1,
tblPhysicsY13.PhysTeacher2, tblPhysicsY13.PhysEffort1,
tblPhysicsY13.PhysAttm1, tblPhysicsY13.PhysComment1,
tblPhysicsY13.PhysEffort2, tblPhysicsY13.PhysAttm2,
tblPhysicsY13.PhysComment2, tblPhysicsY13.Block AS tblPhysicsY13_Block,
TBLRE.BLOCK AS TBLRE_BLOCK, TBLRE.[RE Teacher], TBLRE.REEffort1,
TBLRE.REAttm1, TBLRE.[RE Comment], tblSpanish13.MFLSet,
tblSpanish13.MFLTeacher1, tblSpanish13.MFLEffort AS
tblSpanish13_MFLEffort,
tblSpanish13.MFLAttm AS tblSpanish13_MFLAttm, tblSpanish13.MFLComment AS
tblSpanish13_MFLComment
FROM ((((((((((((((((((tblReport LEFT JOIN tbl6FMaths ON
tblReport.ReportID
= tbl6FMaths.ReportID) LEFT JOIN tbl6FMech ON tblReport.ReportID =
tbl6FMech.ReportID) LEFT JOIN tbl6Mech ON tblReport.ReportID =
tbl6Mech.ReportID) LEFT JOIN tblADArt ON tblReport.ReportID =
tblADArt.ReportID) LEFT JOIN tblBoardingy13 ON tblReport.ReportID =
tblBoardingy13.ReportID) LEFT JOIN [tblBusiness Studies] ON
tblReport.ReportID = [tblBusiness Studies].ReportID) LEFT JOIN tblChemY13
ON
tblReport.ReportID = tblChemY13.ReportID) LEFT JOIN tblEconomics ON
tblReport.ReportID = tblEconomics.ReportID) LEFT JOIN tblEnglishY13 ON
tblReport.ReportID = tblEnglishY13.ReportID) LEFT JOIN tblFoodTec ON
tblReport.ReportID = tblFoodTec.ReportID) LEFT JOIN tblFrenchY13 ON
tblReport.ReportID = tblFrenchY13.ReportID) LEFT JOIN tblGeography ON
tblReport.ReportID = tblGeography.ReportID) LEFT JOIN tblGermanY13 ON
tblReport.ReportID = tblGermanY13.ReportID) LEFT JOIN tblHistory ON
tblReport.ReportID = tblHistory.ReportID) LEFT JOIN tblLS ON
tblReport.ReportID = tblLS.ReportID) LEFT JOIN tblMusicY13 ON
tblReport.ReportID = tblMusicY13.ReportID) LEFT JOIN tblPhysicsY13 ON
tblReport.ReportID = tblPhysicsY13.ReportID) LEFT JOIN TBLRE ON
tblReport.ReportID = TBLRE.ReportID) LEFT JOIN tblSpanish13 ON
tblReport.ReportID = tblSpanish13.ReportID;
Click to expand...
 
J

John Vinson

There are over 12 subjects and each subject is
contained in a table.
Click to expand...

That's the source of your problem.

Storing data in tablenames is INCORRECT DESIGN.

Stop, step back, and normalize your data. You should have *one* table
of Subjects (with "over twelve" rows) and a many to many resolver
table joining students to subjects.

John W. Vinson[MVP]
 
Top