Queries and joins

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a small database for a reporting system. As candidates are
allowed to choose the subjects they take, some of the subject fields will be
empty for most candidates. I used the #2 outer type of join to relate the
tables in a query that consist of candidate name, group, and all the
available subjects. This enables the queries to show the candidates with the
subjects they take, leaving the other fields blank. However, I am unable to
edit or add information in the fields of this query. How do I get around
this problem?
 
What are you trying to update? Are you working in a form? A one-to-many
relationship like you describe would work with a form/subform design.

Good luck

Jeff Boyce
<Access MVP>
 
Hi,

Thank you for the reply. I am trying to update the teacher's comments. The
data type for these fields are "memo". The entire query is read only for
some reason. I have made this query in the same way as the other queries.

I have here a copy of the query in SQL view. Please note that the
attainment1 and attainment2, Effort1 and effort 2 are for two different
teachers. Some subjects are taught by two teachers, each lookin gafter
separate modules.

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;
 
Ande

Based on the SQL you provided, your table structure is well-suited ... for a
spreadsheet! If you want to get the benefit of the tools and functions
Access offers, I'll suggest that you read up on normalization and relational
databases (Access).

For example, your SQL seems to imply that you have a different table for
each subject area (and some other "facts" I can't quite puzzle out).
"Economics_Efforts", "6FMaths", "ChemY13" are all examples of embedding
data in your table names, rather than in your table.

An alternate data structure, and one that Access is designed to easily
support, would look something like:

tlkpSubject (a table listing all valid subjects)
SubjectID (a unique identifying of subjects -- an Autonumber
field is one possibility)
SubjectTitle (e.g., one row each for English, Chemistry, Physics,
Math, ...)

tlkp???? (I don't understand what "6F" and "Y13" represent, but you'd
use a lookup table if they are data)

tblPerson (lists info about persons)


trelRegistrations (lists info about what subjects/other facts persons
sign up for)
RegistrationID
PersonID
SubjectID
????ID
RegistrationDate
SchoolYearEnding
...

These are only guesses, as you've not actually described the underlying data
you are trying to capture and report on.

--
Good luck

Jeff Boyce
<Access MVP>

Ande said:
Hi,

Thank you for the reply. I am trying to update the teacher's comments. The
data type for these fields are "memo". The entire query is read only for
some reason. I have made this query in the same way as the other queries.

I have here a copy of the query in SQL view. Please note that the
attainment1 and attainment2, Effort1 and effort 2 are for two different
teachers. Some subjects are taught by two teachers, each lookin gafter
separate modules.

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;


Jeff Boyce said:
What are you trying to update? Are you working in a form? A one-to-many
relationship like you describe would work with a form/subform design.

Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Back
Top