Subquery

[Daryl2106]

Hi, I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.

I am not good at SQL, so I am in need of help.

The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.

Any help would be greatly appreciated.

Take care,

Daryl

[jzwp11]

Before we can address your query question, I have some questions/concerns about your table structure. If a student is enrolled in a class, why is the attendance table tied to the contact info table and not the student enrollment table? Also, if a course (course inventory table) has many class sections, that describes a one-to-many relationship and therefore, the class sections should be in a separate, but related table. I also have a concern about why you have a field identified as 2012classnumber. If a course can have many class numbers over time that too describes a one-to-many relationship. It sounds like we would benefit from an explanation of how courses, class numbers and class section relate to one another in your organization

[Daryl2106]

Hi, Thanks for the reply and sorry for the late response. I sort of knew linking studentAttendance to ContactInformation was not the correct thing to do. I have since joined the Attendance table[StudentEnrollmentID] to the StudentEnrollmentTable [ID]. I thought that may be the problem but it didn't resolve anything. The 2012CalssNumber field is a hangover from last years DB. I should have deleted when I created the AnnualClassNumbers table.

So

A student is enrolled in several classes stored in StudentEnrollmentTable. These courses are identified by an AnnualClassNumber. In some years the class number is the same but the Term (year for us) is different (1119=2011;1129=2012) hence the need for the AnnualClassNumbers table. The classnumber and section must be kept together and linked to course inventory. The classnumber defines the course and section.

Each record in the Attendance table thus must contain the StudentNumber, AnnualClassNUmber and Date, classessOffered and ClassesAttended. For query purposes I need to be able to include these fields plus, faculty info (name), course title and section to allow faculty to update their attendance.

Should the class section still be in a different table??. Can I do that without significant changes to forms, tables, queries and the like??. Please ask more questions, if this doesn't make sense. I am beyond the limits of my current knowledge.

Thanks and take care,

Dayl

[orange]

Before you make any major changes you should research Normalization. It appears that your database is not really designed to simplify queries. It will require more manual intervention to resolve issues (that are resolvable) than a database designed along Normalization rules.

[Daryl2106]

Hi and thanks.

I think I strayed from what Normalization rules I was following early on just to get the results I needed. I was wondering if you could clarify:

It will require more manual intervention to resolve issues (that are resolvable) than a database designed along Normalization rules.

Does that mean that once properly normalized, it will then require further "retooling"

Thanks and take care,

Daryl

[jzwp11]

Sorry for the delay in my response but I have been interrupted multiple times today as I tried to work on your database. I have to agree with orange's suggestion that you read up on normalization. This site provides a somewhat technical discussion of normalization.

As to your database, if you have to change the table structure (tables and relationships) then more than likely the current forms, queries and reports you may have will be rendered useless and will have to be rebuilt, but having the proper table structure is absolutely necessary for the proper functioning of the database and it will make creating queries much easier for you in the long run.

With that said, I started to rework some parts of your database relative to the courses, sections etc. If you look at the relationship diagram in the attached database you will see that I now have a table for courses, a table for sections and a table that joins sections and courses. It is this last table that then joins to your annual class numbers table. This table along with the contact info table then joins to the student enrollment table. As I mentioned earlier, it is the student enrollment table that should join to the attendance table. I could not make that join, however, because there are a large number of records (297) in the attendance table that have student/classnumber combinations that do not exist in your enrollment table. In other words, you have attendance records for students who are not enrolled in the classnumber they are attending which I assume should not happen. If I delete those records, then I can make the proper join, so that is what I have done in the attached database.

I noticed in the attendance table that you had both an attendance period field and an attendance period ID field. Only one of these fields is necessary, so I created a table to hold the periods (i.e. dates) and linked that to the attendance table via the attendance period ID field.

I also noticed that you had the facultyID field in both the AnnualClassNumber table and the attendance table. If the attendance information is submitted by the instructor then there would be no need to have the facultyID in the attendance table. However, if there are times when a faculty member who is not the instructor submits the attendance data, then having the fields in both tables is appropriate. So that really depends on your organization's rules.

I did not know what some of the other tables were & there relationships, so I stopped there in the redesign.

[orange]

You are in good hands with jzwp11, so I'll bow out.
Good luck with your project.

[Daryl2106]

Thanks so much for doing this. I'll begin the fixing of relationships and forms, etc as I learn more about normalization. Your reply is greatly appreciated. It is clear and thorough. Thanks again.

Daryl

[jzwp11]

You're welcome. Please post back if you have any additional questions about the design.

[Daryl2106]

Hi Jzwp11,

I have (I think) normalized my DB as per your suggestions and example (which was greatly appreciated). I have it doing much of what I had been doing except for a StudentAttendanceByFacultyQuery. It seems when I rebuild the query it runs fine until I save it and open from a form. I then get a "can't make the join" type of error. Here is the SQL:

SELECT ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, AnnualClassNumbers1.ClassNumber, AnnualClassNumbers1.TermNumber, AnnualClassNumbers1.InstructorID, [SectionNEW].[Course Title], SectionNEW.[Class Section], LU_Instructor.LastName, LU_Instructor.FirstName, StudentEnrollmentTable.TempClassessAttended, StudentEnrollmentTable.TempClassessOffered
FROM SectionNEW, LU_Instructor INNER JOIN (SectionNEW INNER JOIN (ContactInformation INNER JOIN (CombinedCourseSectionNEW INNER JOIN (AnnualClassNumbers1 INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON CombinedCourseSectionNEW.PKCCSID = AnnualClassNumbers1.CourseInventoryID) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON ([SectionNEW].PKCoursesID=CombinedCourseSectionNEW.FKCourseID) AND (SectionNEW.PKSectionsID = CombinedCourseSectionNEW.FKSectionID)) ON LU_Instructor.InstructorID = AnnualClassNumbers1.InstructorID
WHERE (((AnnualClassNumbers1.TermNumber)=1129) And ((AnnualClassNumbers1.InstructorID)=Forms!StudentA ttendanceByFacultyQF!cboInstructorName));

I have attached a screen capture of the relationships and the error dialogue.

I still have some cleaning up to do of the tables.

Thanks and take care,

Daryl

[jzwp11]

The problem I see is with the table/query sectionNew shown in red. I would delete it (along with the comma) since you reference sectionNew in other parts of the from clause which should be sufficient.

SELECT ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, AnnualClassNumbers1.ClassNumber, AnnualClassNumbers1.TermNumber, AnnualClassNumbers1.InstructorID, [SectionNEW].[Course Title], SectionNEW.[Class Section], LU_Instructor.LastName, LU_Instructor.FirstName, StudentEnrollmentTable.TempClassessAttended, StudentEnrollmentTable.TempClassessOffered
FROM SectionNEW, LU_Instructor INNER JOIN (SectionNEW INNER JOIN (ContactInformation INNER JOIN (CombinedCourseSectionNEW INNER JOIN (AnnualClassNumbers1 INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON CombinedCourseSectionNEW.PKCCSID = AnnualClassNumbers1.CourseInventoryID) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON ([SectionNEW].PKCoursesID=CombinedCourseSectionNEW.FKCourseID) AND (SectionNEW.PKSectionsID = CombinedCourseSectionNEW.FKSectionID)) ON LU_Instructor.InstructorID = AnnualClassNumbers1.InstructorID
WHERE (((AnnualClassNumbers1.TermNumber)=1129) And ((AnnualClassNumbers1.InstructorID)=Forms!StudentA ttendanceByFacultyQF!cboInstructorName));

[Daryl2106]

Hi,

Thanks for the quick reply. I deleted the text in red but an encountering two addition errors. It is asking to enter values for SectionNEW.CourseTitle and SectionNEW.PKCourseID. I've found and deleted the SectionNEW.CourseTitle (in red below) but can't find the other. Also, these are field that don't exist in the SectionNEW table. If I hit okay to both the query runs but returns no records. When I try to display the query in Design View it can represent the join expression in red below. My knowledge of SQL is minimal, but I am learning from this exchange.

Thanks and take care,

Daryl


SELECT ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, AnnualClassNumbers1.ClassNumber, AnnualClassNumbers1.TermNumber, AnnualClassNumbers1.InstructorID, [SectionNEW].[Course Title], SectionNEW.[Class Section], LU_Instructor.LastName, LU_Instructor.FirstName, StudentEnrollmentTable.TempClassessAttended, StudentEnrollmentTable.TempClassessOffered
FROM SectionNEW, LU_Instructor INNER JOIN (SectionNEW INNER JOIN (ContactInformation INNER JOIN (CombinedCourseSectionNEW INNER JOIN (AnnualClassNumbers1 INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON CombinedCourseSectionNEW.PKCCSID = AnnualClassNumbers1.CourseInventoryID) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON ([SectionNEW].PKCoursesID=CombinedCourseSectionNEW.FKCourseID) AND (SectionNEW.PKSectionsID = CombinedCourseSectionNEW.FKSectionID)) ON LU_Instructor.InstructorID = AnnualClassNumbers1.InstructorID
WHERE (((AnnualClassNumbers1.TermNumber)=1129) And ((AnnualClassNumbers1.InstructorID)=Forms!StudentA ttendanceByFacultyQF!cboInstructorName));

[jzwp11]

See the green text below. Is the field name correctly spelled?


SELECT ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, AnnualClassNumbers1.ClassNumber, AnnualClassNumbers1.TermNumber, AnnualClassNumbers1.InstructorID, [SectionNEW].[Course Title], SectionNEW.[Class Section], LU_Instructor.LastName, LU_Instructor.FirstName, StudentEnrollmentTable.TempClassessAttended, StudentEnrollmentTable.TempClassessOffered
FROM SectionNEW, LU_Instructor INNER JOIN (SectionNEW INNER JOIN (ContactInformation INNER JOIN (CombinedCourseSectionNEW INNER JOIN (AnnualClassNumbers1 INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON CombinedCourseSectionNEW.PKCCSID = AnnualClassNumbers1.CourseInventoryID) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON ([SectionNEW].PKCoursesID=CombinedCourseSectionNEW.FKCourseID) AND (SectionNEW.PKSectionsID = CombinedCourseSectionNEW.FKSectionID)) ON LU_Instructor.InstructorID = AnnualClassNumbers1.InstructorID
WHERE (((AnnualClassNumbers1.TermNumber)=1129) And ((AnnualClassNumbers1.InstructorID)=Forms!StudentA ttendanceByFacultyQF!cboInstructorName));

[Daryl2106]

Hi,

Made sure the spelly is correct. It is still asking to enter values for SectionNEW.CourseTitle and SectionNEW.PKCourseID. These are fields that don't exist in the SectionNEW table. If I hit okay to both the query runs but returns no records.

I'll compress and send along in another post shortly.

Take care,

Daryl

[jzwp11]

These are fields that don't exist in the SectionNEW table.

If the fields do not exist in SectionNew, that is why the query is asking for them--it can't find them. Why do you have fields listed that do not exist or are not calculated in some way?