Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-15
9
349 Views
Last Modified: 2012-05-11
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!



0
Comment
Question by:DymaxionDeveloper
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35138484
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35138571
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 35139067
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 51

Expert Comment

by:HainKurt
ID: 35139141
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
 

Author Comment

by:DymaxionDeveloper
ID: 35139481
This does not work, there error I get is: Syntax Error (Missing Operator) in Query expression <SQL>

Error number 3075
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35139541
which one is not working?
0
 

Author Comment

by:DymaxionDeveloper
ID: 35140157
Both responded by HainKurt
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35140197
did you try the post at http:#a35138571
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35140464
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question