Problem creating recordset with SQL JOIN

[smartin]

I'm writing a Time/Project tracking problem in VB5 using DAO.

I'm trying to use a JOIN SQL statement to create a recordset that has these fields in it: ID, FirstName, and LastName.

The two tables I want to join reside in an Access database named Timesheet1.mdb. The two tables are named Employee and Weeks.

Employee includes a unique ID for each employee, First Name, Last Name, address, etc. etc.

Weeks includes ID which is associated to ID in the Employee Table. The other fields are "Job Number", days of the week "Monday-Sunday", "WkNum", which can be 1-52, and "Year" being the current year for that time sheet,
jobs can span more than one year. The two remaining fields are "For Approval" and "Approved", these being Yes/No fields.

There can be up to 12 records for a given ID, WkNum, and Year, these are for one employees time sheet for that week, each record is time associated with a Job Number.

When the employee is ready to turn in the time sheet for that week all records with ID, WkNum, and Year the same will get the field "For Approval" changed to "Yes".

What I am trying to accomplish is to create a recordset of the two tables "Employee" and "Weeks" JOINED ON ID. I am trying to display two ListBoxes, one with those who have submitted their time sheets and those who have not, all for their supervisor to see.

I am mainly having problems first off with the SQL statement for the JOIN Operation. I keep getting this error: Run-time error '3135: Syntax error in Join Operation. I have tried many different SQL statement
with the same error. Examples below:

String Statement
strSQL6 = "Select Employee.FirstName, Employee.LastName," _ & " Employee.ID FROM Employee" _
& " INNER JOIN ID ON Employee.ID = Weeks.ID WHERE" _
& " Weeks.WkNum = '31' AND Weeks.Year = 2000" _
& " AND Weeks.[For Approval] = YES ORDER BY" _
& " Employee.Lastname"

Adventurous first try. Below simpler statement:

strSQL6 = "Select Employee.Firstname, Employee.Lastname," _
& " Employee.ID FROM Employee" _
& " INNER JOIN ID ON Employee.ID = Weeks.ID"

Recordset Definition
Set rs6 = db.OpenRecordset (strSQL6, dbOpenDynaset)

On both I receive the error mentioned above. Below is complete code for populating the listboxes:

Private Sub cmdFindApprove_Click()
Dim strSQL6 As String
Dim I, NumApprove
Dim strApprove As String
Dim ThisYear As String
'uncomment below to pass year to SQL statement
'ThisYear = Val(Format(Now, "yyyy"))
lstNotReady.Clear
lstReady.Clear
strSQL6 = "Select Employee.FirstName, Employee.LastName," _ & " Employee.ID FROM Employee" _
& " INNER JOIN EmpID ON Employee.ID = Weeks.ID"
Set db = OpenDatabase("c:\timesheet\timesheet1.mdb")
Set rs6 = db.OpenRecordset(strSQL6, dbOpenDynaset)
rs6.MoveLast
rs6.MoveFirst
NumApprove = rs6.RecordCount

For I = 1 To NumApprove
If rs6.EOF Then Exit Sub
If rs6.Fields("For Approval") = "No" Then
strApprove = rs6.Fields("FirstName") & " " & rs6.Fields("LastName")
lstNotReady.AddItem strApprove
Else
strApprove = rs6.Fields("FirstName") & " " & rs6.Fields("LastName")
lstReady.AddItem strApprove
End If

rs6.MoveNext
Next I

End Sub

I am also confused as wheter to use an INNER JOIN, LEFT JOIN etc. because I just want to populate the listboxes with one listing (not up to twelve with same ID) if employee did or didn't submit time sheet for approval. Also I noticed that on some Microsoft examples and in a book on SQL there is a ";" character at the end of the SQL statement.

Any suggestions would be greatly apprciated as I have run up against a brick
wall.

Thanks,

[davidrobin]

The first problem you will be having is you should specify the table name after the INNER JOIN command therefore

strSQL6 = "Select Employee.FirstName, Employee.LastName," _ & " Employee.ID FROM Employee" _
& " INNER JOIN ID ON Employee.ID = Weeks.ID WHERE" _
& " Weeks.WkNum = '31' AND Weeks.Year = 2000" _
& " AND Weeks.[For Approval] = YES ORDER BY" _
& " Employee.Lastname"

should really read

INNER JOIN Weeks ON Employee.ID = Weeks.ID WHERE

if I am breaking up a query I usually do it like so

strSQL6 = "Select Employee.FirstName, Employee.LastName,"
strSQL6 = strSQL6 & " Employee.ID FROM Employee"
strSQL6 = strSQL6 & " INNER JOIN ID ON Employee.ID = Weeks.ID WHERE"
strSQL6 = strSQL6 & " Weeks.WkNum = '31' AND Weeks.Year = 2000"
strSQL6 = strSQL6 & " AND Weeks.[For Approval] = YES ORDER BY"
strSQL6 = strSQL6 & " Employee.Lastname"

Also is 'For Approval' a boolean field, try using true - false or 1 - 0 respectively.