How do I join 4 Tables using VB6 and MS Access using SQL?

I have 4 tables; Shifts, Employees, Locations, and Employee Types.

My Goal is to Select a group of shifts that fall within a date criteria and join all of the above tables. Here's what I came up with but it is obviously not working or I wouldn't be posting here :-(

Select * From Shifts LEFT JOIN Employees ON Employees.EmployeeID=Shifts.EmployeeID LEFT JOIN Location ON Location.LocationID=Shifts.LocationID LEFT JOIN EmployeeTypes ON EmployeeTypes.TypeID=Employees.EmployeeType Where Shifts.Begin < #16-Mar-2011 12:00:00 AM# And Shifts.End > #15-Mar-2011 12:00:00 AM# And (Shifts.Type = 2 OR Shifts.Type = 3)

The Location Table, may or may not have an entry in the table that corresponds with the Shifts.LocationID (ie: I want to support a location ID of 0 in the shifts table)

Some help with this SQL would really be appreciated!



DymaxionDeveloperAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

SELECT Shifts.*
FROM ((Shifts LEFT JOIN Employees ON Shifts.EmployeeID = Employees.EmployeeID) LEFT JOIN Location ON Shifts.LocationID = Location.LocationID) LEFT JOIN EmployeeTypes ON Employees.EmployeeType = EmployeeTypes.TypeID
Where Shifts.Begin < #16-Mar-2011 12:00:00 AM# And Shifts.End > #15-Mar-2011 12:00:00 AM# And (Shifts.Type = 2 OR Shifts.Type = 3)
0
 
peter57rCommented:
I can't see anything wrong in principle with the query, but where multiple joins are involved, Access requires lots of ( ) .
You would be better off creating the same query in the Access query designer and then copying the sql from that.
0
 
HainKurtSr. System AnalystCommented:
do you get any data for thisa

Select *
From
Shifts LEFT JOIN Employees ON Employees.EmployeeID=Shifts.EmployeeID
LEFT JOIN Location ON Location.LocationID=Shifts.LocationID
LEFT JOIN EmployeeTypes ON EmployeeTypes.TypeID=Employees.EmployeeType
Where (Shifts.Type = 2 OR Shifts.Type = 3)

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
HainKurtSr. System AnalystCommented:
try this too

Select * 
  From (select * 
          from Shifts 
         where Shifts.Begin < #16-Mar-2011 12:00:00 AM# And Shifts.End > #15-Mar-2011 12:00:00 AM# And (Shifts.Type = 2 OR Shifts.Type = 3)
       ) as s 
       LEFT JOIN Employees as e ON e.EmployeeID=s.EmployeeID
       LEFT JOIN Location as l ON l.LocationID=s.LocationID 
       LEFT JOIN EmployeeTypes as et ON et.TypeID=e.EmployeeType

Open in new window

0
 
DymaxionDeveloperAuthor Commented:
This does not work, there error I get is: Syntax Error (Missing Operator) in Query expression <SQL>

Error number 3075
0
 
Rey Obrero (Capricorn1)Commented:
which one is not working?
0
 
DymaxionDeveloperAuthor Commented:
Both responded by HainKurt
0
 
Rey Obrero (Capricorn1)Commented:
did you try the post at http:#a35138571
0
 
HainKurtSr. System AnalystCommented:
put some (..)
Select * 
  From (((select * 
          from Shifts 
         where Shifts.Begin < #16-Mar-2011 12:00:00 AM# And Shifts.End > #15-Mar-2011 12:00:00 AM# And (Shifts.Type = 2 OR Shifts.Type = 3)
       ) as s 
       LEFT JOIN Employees as e ON e.EmployeeID=s.EmployeeID) 
LEFT JOIN Location as l ON l.LocationID=s.LocationID)
LEFT JOIN EmployeeTypes as et ON et.TypeID=e.EmployeeType

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.