Error 3061, SQL problem

[stevenblanc]

Hey folks,

So I'm building a query for export to excel based on user filters and so forth.

It was working dandily, until I threw in some code to replace field names with values from another table. Now rather than look for problems with the vba, i'd like to figure out what the hell is wrong with the SQL.

So here's the SQL:

SELECT 
tblCourseRecords.RecordID AS RecordID, 
tblDepartments.DepartmentID AS DepartmentID, 
tblSemesters.semTitle AS Semester, 
tblSemesters.SemYear AS Sem_Year, 
tblCourseRecords.courseID AS Course, 
tblCourseRecords.sectionID as Sections, 
tblCourseRecords.facultyID as Instructor, 
tblCourseRecords.dateEntry as Date_Entered, 
tblCourseRecords.dateModify as Date_Modified, 
tblCourseRecords.numEnrollment, 
tblCourseRecords.numPed1 AS Number_Lap_Reports, 
tblCourseRecords.numPed2 AS Number_Exams_Multiple_Choice, 
tblCourseRecords.numPed3 AS Number_Exams_One_Hour, 
tblCourseRecords.numPed4 AS Number_Exams_Two_Hour, 
tblCourseRecords.numPed5 AS Number_Placeholder1, 
tblCourseRecords.numPed6 AS Number_Placeholder2, 
tblCourseRecords.numPed7 AS Number_Placerholder3, 
tblCourseRecords.numPed8 AS Number_Placeholder4, 
tblCourseRecords.numPed9 AS Number_Placerholder5, 
tblCourseRecords.numPed10 AS Number_Placerholder6, 
tblCourseRecords.numPed11 AS Number_Placerholder7, 
tblCourseRecords.numPed12 AS Number_Placerholder8, 
tblCourseRecords.numPed13 AS Number_Placerholder9, 
tblCourseRecords.numPed14 AS Number_Placerholder10, 
tblCourseRecords.numPed15 AS Number_One_1_or_1_5_hour_lab, 
tblCourseRecords.numPed16 AS Number_Two_1_or_1_5_hour_labs, 
tblCourseRecords.numPed17 AS Number_One_2_hour_lab, 
tblCourseRecords.numPed18 AS Number_One_3_hour_labs, 
tblCourseRecords.numPed19 AS Number_Two_2_hour_labs, 
tblCourseRecords.notePed1 AS Note_Lap_Reports, 
tblCourseRecords.notePed2 AS Note_Exams_Multiple_Choice, 
tblCourseRecords.notePed3 AS Note_Exams_One_Hour, 
tblCourseRecords.notePed4 AS Note_Exams_Two_Hour, 
tblCourseRecords.notePed5 AS Note_Placeholder1, 
tblCourseRecords.notePed6 AS Note_Placeholder2, 
tblCourseRecords.notePed7 AS Note_Placerholder3, 
tblCourseRecords.notePed8 AS Note_Placeholder4, 
tblCourseRecords.notePed9 AS Note_Placerholder5, 
tblCourseRecords.notePed10 AS Note_Placerholder6, 
tblCourseRecords.notePed11 AS Note_Placerholder7, 
tblCourseRecords.notePed12 AS Note_Placerholder8, 
tblCourseRecords.notePed13 AS Note_Placerholder9, 
tblCourseRecords.notePed14 AS Note_Placerholder10, 
tblCourseRecords.notePed15 AS Note_One_1_or_1_5_hour_lab, 
tblCourseRecords.notePed16 AS Note_Two_1_or_1_5_hour_labs, 
tblCourseRecords.notePed17 AS Note_One_2_hour_lab, 
tblCourseRecords.notePed18 AS Note_One_3_hour_labs, 
tblCourseRecords.notePed19 AS Note_Two_2_hour_labs, 
tblCourseRecords.timePed1 AS Time_Lap_Reports, 
tblCourseRecords.timePed2 AS Time_Exams_Multiple_Choice, 
tblCourseRecords.timePed3 AS Time_Exams_One_Hour, 
tblCourseRecords.timePed4 AS Time_Exams_Two_Hour, 
tblCourseRecords.timePed5 AS Time_Placeholder1, 
tblCourseRecords.timePed6 AS Time_Placeholder2, 
tblCourseRecords.timePed7 AS Time_Placerholder3, 
tblCourseRecords.timePed8 AS Time_Placeholder4, 
tblCourseRecords.timePed9 AS Time_Placerholder5, 
tblCourseRecords.timePed10 AS Time_Placerholder6, 
tblCourseRecords.timePed11 AS Time_Placerholder7, 
tblCourseRecords.timePed12 AS Time_Placerholder8, 
tblCourseRecords.timePed13 AS Time_Placerholder9, 
tblCourseRecords.timePed14 AS Time_Placerholder10, 
tblCourseRecords.timePed15 AS Time_One_1_or_1_5_hour_lab, 
tblCourseRecords.timePed16 AS Time_Two_1_or_1_5_hour_labs, 
tblCourseRecords.timePed17 AS Time_One_2_hour_lab, 
tblCourseRecords.timePed18 AS Time_One_3_hour_labs, 
tblCourseRecords.timePed19 AS Time_Two_2_hour_labs, 
tblCourseRecords.timeTotal as Total_Hours 

FROM 
tblCourseRecords, 
tblSemesters, 
tblCourses, 
tblDepartments 

WHERE 
tblSemesters.semesterID = tblCourseRecords.semesterID 
AND 
tblCourses.courseID = tblCourseRecords.courseID 
AND 
tblDepartments.departmentID = tblCourses.courseDepartment 

ORDER BY 
tblCourseRecords.recordID 
;

It's all one solid mass, but I figure no one would brave it that way

Basically the only thing I've done to it since the working version is include the "As *" for the numPedi, notePedi, and timePedi

The error I get is the following:

Error Number: 3061
Error Description: Too few parameters. Expected 30.

 

[AccessMSSQL]

First off, where are you join statements?
....
FROM
tblCourseRecords INNER JOIN tblSemesters
ON tblCourseRecords.SemesterID = tblSemesters.SemesterID
INNER JOIN tblCourses
ON tblCourses.CourseID = tblCourseRecords.CourseID
INNER JOIN tblDepartments
ON tblDepartments.DepartmentID = tblCourses.courseDepartment 'hope this is an integer field??

ORDER BY
tblCourseRecords.recordID
;

 

[stevenblanc]

They are all string fields.

SELECT 
tblCourseRecords.RecordID AS RecordID, 
tblDepartments.DepartmentID AS DepartmentID, 
tblSemesters.semTitle AS Semester, 
tblSemesters.SemYear AS Sem_Year, 
tblCourseRecords.courseID AS Course, 
tblCourseRecords.sectionID as Sections, 
tblCourseRecords.facultyID as Instructor, 
tblCourseRecords.dateEntry as Date_Entered, 
tblCourseRecords.dateModify as Date_Modified, 
tblCourseRecords.numEnrollment, 
tblCourseRecords.numPed1 AS Number_Lap_Reports, 
tblCourseRecords.numPed2 AS Number_Exams_Multiple_Choice, 
tblCourseRecords.numPed3 AS Number_Exams_One_Hour, 
tblCourseRecords.numPed4 AS Number_Exams_Two_Hour, 
tblCourseRecords.numPed5 AS Number_Placeholder1, 
tblCourseRecords.numPed6 AS Number_Placeholder2, 
tblCourseRecords.numPed7 AS Number_Placerholder3, 
tblCourseRecords.numPed8 AS Number_Placeholder4, 
tblCourseRecords.numPed9 AS Number_Placerholder5, 
tblCourseRecords.numPed10 AS Number_Placerholder6, 
tblCourseRecords.numPed11 AS Number_Placerholder7, 
tblCourseRecords.numPed12 AS Number_Placerholder8, 
tblCourseRecords.numPed13 AS Number_Placerholder9, 
tblCourseRecords.numPed14 AS Number_Placerholder10, 
tblCourseRecords.numPed15 AS Number_One_1_or_1_5_hour_lab, 
tblCourseRecords.numPed16 AS Number_Two_1_or_1_5_hour_labs, 
tblCourseRecords.numPed17 AS Number_One_2_hour_lab, 
tblCourseRecords.numPed18 AS Number_One_3_hour_labs, 
tblCourseRecords.numPed19 AS Number_Two_2_hour_labs, 
tblCourseRecords.notePed1 AS Note_Lap_Reports, 
tblCourseRecords.notePed2 AS Note_Exams_Multiple_Choice, 
tblCourseRecords.notePed3 AS Note_Exams_One_Hour, 
tblCourseRecords.notePed4 AS Note_Exams_Two_Hour, 
tblCourseRecords.notePed5 AS Note_Placeholder1, 
tblCourseRecords.notePed6 AS Note_Placeholder2, 
tblCourseRecords.notePed7 AS Note_Placerholder3, 
tblCourseRecords.notePed8 AS Note_Placeholder4, 
tblCourseRecords.notePed9 AS Note_Placerholder5, 
tblCourseRecords.notePed10 AS Note_Placerholder6, 
tblCourseRecords.notePed11 AS Note_Placerholder7, 
tblCourseRecords.notePed12 AS Note_Placerholder8, 
tblCourseRecords.notePed13 AS Note_Placerholder9, 
tblCourseRecords.notePed14 AS Note_Placerholder10, 
tblCourseRecords.notePed15 AS Note_One_1_or_1_5_hour_lab, 
tblCourseRecords.notePed16 AS Note_Two_1_or_1_5_hour_labs, 
tblCourseRecords.notePed17 AS Note_One_2_hour_lab, 
tblCourseRecords.notePed18 AS Note_One_3_hour_labs, 
tblCourseRecords.notePed19 AS Note_Two_2_hour_labs, 
tblCourseRecords.timePed1 AS Time_Lap_Reports, 
tblCourseRecords.timePed2 AS Time_Exams_Multiple_Choice, 
tblCourseRecords.timePed3 AS Time_Exams_One_Hour, 
tblCourseRecords.timePed4 AS Time_Exams_Two_Hour, 
tblCourseRecords.timePed5 AS Time_Placeholder1, 
tblCourseRecords.timePed6 AS Time_Placeholder2, 
tblCourseRecords.timePed7 AS Time_Placerholder3, 
tblCourseRecords.timePed8 AS Time_Placeholder4, 
tblCourseRecords.timePed9 AS Time_Placerholder5, 
tblCourseRecords.timePed10 AS Time_Placerholder6, 
tblCourseRecords.timePed11 AS Time_Placerholder7, 
tblCourseRecords.timePed12 AS Time_Placerholder8, 
tblCourseRecords.timePed13 AS Time_Placerholder9, 
tblCourseRecords.timePed14 AS Time_Placerholder10, 
tblCourseRecords.timePed15 AS Time_One_1_or_1_5_hour_lab, 
tblCourseRecords.timePed16 AS Time_Two_1_or_1_5_hour_labs, 
tblCourseRecords.timePed17 AS Time_One_2_hour_lab, 
tblCourseRecords.timePed18 AS Time_One_3_hour_labs, 
tblCourseRecords.timePed19 AS Time_Two_2_hour_labs, 
tblCourseRecords.timeTotal as Total_Hours 
FROM 
tblCourseRecords INNER JOIN tblSemesters 
ON tblCourseRecords.SemesterID = tblSemesters.SemesterID 
INNER JOIN tblCourses 
ON tblCourses.CourseID = tblCourseRecords.CourseID 
INNER JOIN tblDepartments 
ON tblDepartments.DepartmentID = tblCourses.courseDepartment 
ORDER BY 
tblCourseRecords.recordID ;

Results in Error 3075,
Syntax Error, missing operator, in query expression

 

[boblarson]

First off, where are you join statements?
....
FROM
tblCourseRecords INNER JOIN tblSemesters
ON tblCourseRecords.SemesterID = tblSemesters.SemesterID
INNER JOIN tblCourses
ON tblCourses.CourseID = tblCourseRecords.CourseID
INNER JOIN tblDepartments
ON tblDepartments.DepartmentID = tblCourses.courseDepartment 'hope this is an integer field??

ORDER BY
tblCourseRecords.recordID
;

Exactly, the joins are missing as a starting point. Fix those like AccessMSSQL has suggested and go from there. Are you trying to write this all manually or are you using the QBE (Query by example) grid?

 

[stevenblanc]

This works:

SELECT tblCourseRecords.RecordID AS RecordID, 
tblDepartments.DepartmentID AS DepartmentID, 
tblSemesters.semTitle AS Semester, 
tblSemesters.SemYear AS Sem_Year, 
tblCourseRecords.courseID AS Course, 
tblCourseRecords.sectionID as Sections, 
tblCourseRecords.facultyID as Instructor, 
tblCourseRecords.dateEntry as Date_Entered, 
tblCourseRecords.dateModify as Date_Modified, 
tblCourseRecords.numEnrollment, 
tblCourseRecords.numPed1, 
tblCourseRecords.numPed2, tblCourseRecords.numPed3, 
tblCourseRecords.numPed4, 
tblCourseRecords.numPed15, 
tblCourseRecords.numPed16, 
tblCourseRecords.numPed17, 
tblCourseRecords.numPed18, 
tblCourseRecords.numPed19, 
tblCourseRecords.notePed1, tblCourseRecords.notePed2, 
tblCourseRecords.notePed3, tblCourseRecords.notePed4, 
tblCourseRecords.notePed15, tblCourseRecords.notePed16, 
tblCourseRecords.notePed17, tblCourseRecords.notePed18, 
tblCourseRecords.notePed19, tblCourseRecords.timePed1,  
tblCourseRecords.timePed2, tblCourseRecords.timePed3, 
tblCourseRecords.timePed4, tblCourseRecords.timePed15, 
tblCourseRecords.timePed16, tblCourseRecords.timePed17, 
tblCourseRecords.timePed18, tblCourseRecords.timePed19, 
tblCourseRecords.timeTotal as Total_Hours 

FROM 
tblCourseRecords, 
tblSemesters, 
tblCourses, 
tblDepartments 

WHERE tblSemesters.semesterID = tblCourseRecords.semesterID 
AND 
tblCourses.courseID = tblCourseRecords.courseID 
AND 
tblDepartments.departmentID = tblCourses.courseDepartment 

ORDER BY 
tblCourseRecords.recordID ;

This doesn't:

SELECT tblCourseRecords.RecordID AS RecordID, 
tblDepartments.DepartmentID AS DepartmentID, 
tblSemesters.semTitle AS Semester, 
tblSemesters.SemYear AS Sem_Year, 
tblCourseRecords.courseID AS Course, 
tblCourseRecords.sectionID as Sections, 
tblCourseRecords.facultyID as Instructor, 
tblCourseRecords.dateEntry as Date_Entered, 
tblCourseRecords.dateModify as Date_Modified, 
tblCourseRecords.numEnrollment, 
tblCourseRecords.numPed1 AS Number_Lap_Reports, 
tblCourseRecords.numPed2 AS Number_Exams_Multiple_Choice, 
tblCourseRecords.numPed3 AS Number_Exams_One_Hour, 
tblCourseRecords.numPed4 AS Number_Exams_Two_Hour, 
tblCourseRecords.numPed5 AS Number_Placeholder1, 
tblCourseRecords.numPed6 AS Number_Placeholder2, 
tblCourseRecords.numPed7 AS Number_Placerholder3, 
tblCourseRecords.numPed8 AS Number_Placeholder4, 
tblCourseRecords.numPed9 AS Number_Placerholder5, 
tblCourseRecords.numPed10 AS Number_Placerholder6, 
tblCourseRecords.numPed11 AS Number_Placerholder7, 
tblCourseRecords.numPed12 AS Number_Placerholder8, 
tblCourseRecords.numPed13 AS Number_Placerholder9, 
tblCourseRecords.numPed14 AS Number_Placerholder10, 
tblCourseRecords.numPed15 AS Number_One_1_or_1_5_hour_lab, 
tblCourseRecords.numPed16 AS Number_Two_1_or_1_5_hour_labs, 
tblCourseRecords.numPed17 AS Number_One_2_hour_lab, 
tblCourseRecords.numPed18 AS Number_One_3_hour_labs, 
tblCourseRecords.numPed19 AS Number_Two_2_hour_labs, 
tblCourseRecords.notePed1 AS Note_Lap_Reports, 
tblCourseRecords.notePed2 AS Note_Exams_Multiple_Choice, 
tblCourseRecords.notePed3 AS Note_Exams_One_Hour, 
tblCourseRecords.notePed4 AS Note_Exams_Two_Hour, 
tblCourseRecords.notePed5 AS Note_Placeholder1, 
tblCourseRecords.notePed6 AS Note_Placeholder2, 
tblCourseRecords.notePed7 AS Note_Placerholder3, 
tblCourseRecords.notePed8 AS Note_Placeholder4, 
tblCourseRecords.notePed9 AS Note_Placerholder5, 
tblCourseRecords.notePed10 AS Note_Placerholder6, 
tblCourseRecords.notePed11 AS Note_Placerholder7, 
tblCourseRecords.notePed12 AS Note_Placerholder8, 
tblCourseRecords.notePed13 AS Note_Placerholder9, 
tblCourseRecords.notePed14 AS Note_Placerholder10, 
tblCourseRecords.notePed15 AS Note_One_1_or_1_5_hour_lab, 
tblCourseRecords.notePed16 AS Note_Two_1_or_1_5_hour_labs, 
tblCourseRecords.notePed17 AS Note_One_2_hour_lab, 
tblCourseRecords.notePed18 AS Note_One_3_hour_labs, 
tblCourseRecords.notePed19 AS Note_Two_2_hour_labs, 
tblCourseRecords.timePed1 AS Time_Lap_Reports, 
tblCourseRecords.timePed2 AS Time_Exams_Multiple_Choice, 
tblCourseRecords.timePed3 AS Time_Exams_One_Hour, 
tblCourseRecords.timePed4 AS Time_Exams_Two_Hour, 
tblCourseRecords.timePed5 AS Time_Placeholder1, 
tblCourseRecords.timePed6 AS Time_Placeholder2, 
tblCourseRecords.timePed7 AS Time_Placerholder3, 
tblCourseRecords.timePed8 AS Time_Placeholder4, 
tblCourseRecords.timePed9 AS Time_Placerholder5, 
tblCourseRecords.timePed10 AS Time_Placerholder6, 
tblCourseRecords.timePed11 AS Time_Placerholder7, 
tblCourseRecords.timePed12 AS Time_Placerholder8, 
tblCourseRecords.timePed13 AS Time_Placerholder9, 
tblCourseRecords.timePed14 AS Time_Placerholder10, 
tblCourseRecords.timePed15 AS Time_One_1_or_1_5_hour_lab, 
tblCourseRecords.timePed16 AS Time_Two_1_or_1_5_hour_labs, 
tblCourseRecords.timePed17 AS Time_One_2_hour_lab, 
tblCourseRecords.timePed18 AS Time_One_3_hour_labs, 
tblCourseRecords.timePed19 AS Time_Two_2_hour_labs, 
tblCourseRecords.timeTotal as Total_Hours 

FROM tblCourseRecords, 
tblSemesters, 
tblCourses, 
tblDepartments 

WHERE 
tblSemesters.semesterID = tblCourseRecords.semesterID 
AND 
tblCourses.courseID = tblCourseRecords.courseID 
AND 
tblDepartments.departmentID = tblCourses.courseDepartment 

ORDER BY 
tblCourseRecords.recordID ;

And then when I paste the two of them side by side, I realize I am a complete idiot and half of those fields don't currently exist on tblCourseRecords.

Sorry for wasting everyones time!

Cheers,

Steven

 

[boblarson]

Your first one "which works" is still not written correctly. You should be using JOINS on your tables and not relying soley on the Where clause. Very INEFFICIENT the way you currently have it written.

 

[stevenblanc]

Bob,

I'm building it all manually and probably not in the most efficient way. I need the user to be able to filter it and sort with button presses. Also the "AS" Titles are pulled from another table so that the user can set names for each of the fields when they export them without interfering with my coded.

The biggest problem is that I'm building the DB from scratch, and the more I plan the more I find additional features being needed. I will still have to allow the user to add fields to handle additional pedagogical items which will require both table and form modification using vb code.

This is my construction at the present. I'm trying to sort the joins out. When I do it as recommended by AccessMSSQL suggests the query fails.

The code for this particular construction is as follows:

Private Sub btnTestSQL2_Click()
    Dim strExport As String
    Dim PedArray As Variant
    Dim i As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim col()
    
    'This ensures that the button cannot be clicked a second time before the qry is deleted

    strExport = "SELECT tblCourseRecords.RecordID AS RecordID, tblDepartments.DepartmentID AS DepartmentID, tblSemesters.semTitle AS Semester, tblSemesters.SemYear AS Sem_Year, tblCourseRecords.courseID AS Course, tblCourseRecords.sectionID as Sections, tblCourseRecords.facultyID as Instructor, tblCourseRecords.dateEntry as Date_Entered, tblCourseRecords.dateModify as Date_Modified, tblCourseRecords.numEnrollment, "
    strEND = ";"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblGuidelines")
    
    'First Loop adds names for the number of pedagogical items
    i = 0
    Do While Not rs.EOF
        i = i + 1
        ReDim Preserve col(i)
        col(i) = rs![pedTitle]
        col(i) = Replace(col(i), " ", "_")
        col(i) = Replace(col(i), ",", "")
        col(i) = Replace(col(i), ")", "")
        col(i) = Replace(col(i), "(", "")
        col(i) = Replace(col(i), ".", "_")
        'PedArray = Array(col(i))
        rs.MoveNext
        
        If fieldexists("tblCourseRecords", "numPed" & i) Then
            strExport = strExport & "tblCourseRecords.numPed" & i & " AS Number_" & col(i) & ", " & vbNewLine
        End If
    Loop

    'Second Loop adds names for the notes for pedagogical items
    i = 0
    rs.MoveFirst
    Do While Not rs.EOF
        i = i + 1
        ReDim Preserve col(i)
        col(i) = rs![pedTitle]
        col(i) = Replace(col(i), " ", "_")
        col(i) = Replace(col(i), ",", "")
        col(i) = Replace(col(i), ")", "")
        col(i) = Replace(col(i), "(", "")
        col(i) = Replace(col(i), ".", "_")
        'PedArray = Array(col(i))
        rs.MoveNext
        
        If fieldexists("tblCourseRecords", "notePed" & i) Then
            strExport = strExport & "tblCourseRecords.notePed" & i & " AS Note_" & col(i) & ", " & vbNewLine
        End If
    Loop
    
    'Third Loop adds names for the time for pedagogical items
    i = 0
    rs.MoveFirst
    Do While Not rs.EOF
        i = i + 1
        ReDim Preserve col(i)
        col(i) = rs![pedTitle]
        col(i) = Replace(col(i), " ", "_")
        col(i) = Replace(col(i), ",", "")
        col(i) = Replace(col(i), ")", "")
        col(i) = Replace(col(i), "(", "")
        col(i) = Replace(col(i), ".", "_")
        'PedArray = Array(col(i))
        rs.MoveNext
        
        
        If fieldexists("tblCourseRecords", "timePed" & i) Then
            strExport = strExport & "tblCourseRecords.timePed" & i & " AS Time_" & col(i) & ", " & vbNewLine
        End If
    
    Loop
    

    strExport = strExport & "tblCourseRecords.timeTotal as Total_Hours FROM tblCourseRecords INNER JOIN tblSemesters ON tblCourseRecords.SemesterID = tblSemesters.SemesterID INNER JOIN tblCourses ON tblCourses.CourseID = tblCourseRecords.CourseID INNER JOIN tblDepartments ON tblDepartments.DepartmentID = tblCourses.courseDepartment "
    
   
    strExport = strExport & strWHERE & strORDERBY & strEND
    
    lstTestSQL2.RowSource = strExport
    txtTestSQL = strExport

End Sub

In this test form, there are no values for strWHERE and strORDERBY as those are generally used for filtering. As I am attempting to replace the original WHERE statement with JOINs it will remain blank.

The output from this is as follows:

SELECT 
tblCourseRecords.RecordID AS RecordID, 
tblDepartments.DepartmentID AS DepartmentID, 
tblSemesters.semTitle AS Semester, 
tblSemesters.SemYear AS Sem_Year, 
tblCourseRecords.courseID AS Course, 
tblCourseRecords.sectionID as Sections, 
tblCourseRecords.facultyID as Instructor, 
tblCourseRecords.dateEntry as Date_Entered, 
tblCourseRecords.dateModify as Date_Modified, 
tblCourseRecords.numEnrollment, 
tblCourseRecords.numPed1 AS Number_Lap_Reports, 
tblCourseRecords.numPed2 AS Number_Exams_Multiple_Choice, 
tblCourseRecords.numPed3 AS Number_Exams_One_Hour, 
tblCourseRecords.numPed4 AS Number_Exams_Two_Hour, 
tblCourseRecords.notePed1 AS Note_Lap_Reports, 
tblCourseRecords.notePed2 AS Note_Exams_Multiple_Choice, 
tblCourseRecords.notePed3 AS Note_Exams_One_Hour, 
tblCourseRecords.notePed4 AS Note_Exams_Two_Hour, 
tblCourseRecords.timePed1 AS Time_Lap_Reports, 
tblCourseRecords.timePed2 AS Time_Exams_Multiple_Choice, 
tblCourseRecords.timePed3 AS Time_Exams_One_Hour, 
tblCourseRecords.timePed4 AS Time_Exams_Two_Hour, 
tblCourseRecords.timeTotal as Total_Hours 

FROM tblCourseRecords 

INNER JOIN tblSemesters 
ON tblCourseRecords.SemesterID = tblSemesters.SemesterID 

INNER JOIN tblCourses 
ON tblCourses.CourseID = tblCourseRecords.CourseID 

INNER JOIN tblDepartments 
ON tblDepartments.DepartmentID = tblCourses.courseDepartment ;

SemesterID, CourseID, DepartmentID, courseDepartment are all String Fields if this makes a difference. These fields are all entered into tblCourseRecords through comboboxes which take their source from DepartmentID.

EDIT: Looking at the above code it seems unecessary to join tblCourseRecords, and tblDepartments. This may be so at the moment, but as the reports are not generated for my use I would like to simply be able to drop in the additional required fields where necessary if requested.

 

[stevenblanc]

Just as a matter of curiosity. I have see a number of articles which suggest the use of joins using a comma separated tables and the WHERE statement. Which is the way I have always joined tables.

Where does the inefficiency stem from?

For example:
http://stackoverflow.com/questions/121631/inner-join-vs-where

EDIT:
After doing a little research it seems that the speed is the same but joins offer more flexibility. In this case the most suitable join would be LEFT JOIN as this would allow me to display all course records even if courses or semesters are removed from their tables in the future.

Even so, I can only get the statement to work with one JOIN.

 

[boblarson]

Even so, I can only get the statement to work with one JOIN.

Are you trying to do it manually by writing it or are you using the query design tool? The design tool will help you get the joins correct and will simplify things. It SOUNDS like, from your description, that you are trying to write the SQL manually and, while that is all fine and dandy, it is the hardest way to the solution.

 

[stevenblanc]

Brackets to the rescue.... So it should actually look like this:

FROM (((tblCourseRecords
LEFT JOIN tblSemesters
ON tblCourseRecords.SemesterID = tblSemesters.SemesterID)

LEFT JOIN tblDepartments
ON tblCourseRecords.DepartmentID = tblDepartments.DepartmentID)

LEFT JOIN tblCourses
ON tblCourseRecords.CourseID = tblDCourses.CourseID)

 

[stevenblanc]

I like doing things manually. Helps me understand whats going on.

 

[AccessMSSQL]

If you are used to writing queries in SQL Server using Transact SQL the translation to Access isn't quite the same. In my opinion, I don't like the way Access translates the syntax - but that's probably why you ran into the problem. Access writes joins a bit backwards from SQL Server and also inserts all of the parenthesis in there, sometimes un-necessarily.