Subquery

[Daryl2106]

Hi, (Sorry, I got taken away from my desk)



That is what I don't understand. I did not write the SQL; it was created through QueryDesign. I have deleted and rewrote the query and the same thing happened.

As I create the query it will run fine until I add the SectionNEW table. When I run it, no records are returned but an EXP1000 field is created and it begins asking for those fields mentioned above that don't exist.

This query is behind the Form StudentAttendanceByFaculty. It allows faculty to enter new biweekly attendance records for the student in their classes.

Thanks again and take care,

Daryl

[jzwp11]

You have redundant data fields (roomoccupancy and roomoccupancyClassName) in 3 tables : CourseNew, SectionNew, CombinedCourseSectionNew. By normalization rules, you should only have the data in 1 table.

I would recommend starting fresh, so I created a new database (attached) and imported just your tables into it. The first thing you need to do is to remove any tables that are no longer needed (based on the data migration I did earlier for you). Then you will need to fix the table structure. Only after the table structure is fixed should you work on the forms.


Once you have removed the tables that are no longer needed, please post that database so that we can begin the work of fixing the table structure.

[Daryl2106]

Hi,

Thanks for the reply and attachment. I cleaned up the tables and kept only the ones I feel will be needed. I also deleted the redundant fields. I had to create the three "NEW" tables (with the redundant fields) because the course names, section numbers and course section table were not returning the correct Faculty-CourseName-Section results combination. They do now but I had forgot to delete those redundant fields.

Take care,

Daryl

[jzwp11]

There are still some normalization issue. In the student attendance table, you should not have the studentID or the class number fields. That information is available via the joins to the other tables. Additionally, for the attendance period, it should be in a separate but related table and only the AttendancePeriodID field should be in the student attendance table. Regarding the facultyID, the instructor is already associated with the class numbe so unless the facultyID represents a different person from the instructor, you do not need the facultyID field in the student attendance table. Also you still have some spacts and special characters in some of your field names: StudentID&AnnualCN and Student#&AnnualCNID in the studentenrollmenttable and studentnumber&AnnualCN in the student attendance table. Out of curiosity, these fields look somewhat similar and may be "constructed" values. If the values are constructed they would be considered as calculated values and thus should not be stored in the table but rather constructed on the fly when needed (from the fields from which the values come). The & and # signs have special significance in Access and are considered reserved symbols and therefore should not be used in table or field names. You also have spaces in the Course title and class section fields of the coursesnew and sectionnew tables respectively; having spaces requires you to enclose the field names in square brackets whenever you reference the field names. If you fail to enclose the field names in square brackets you will end up with errors in your queries, macros and code.

[Daryl2106]

Hi,

Thanks for all your help. I have fixed the Normalization problems (...I think, for now, anyway) and was able to rewrite the query and it works fine.

Thanks for all your help.

Take care,

Daryl

[jzwp11]

Glad to hear that you got it all worked out. Good luck with your project.