?
Solved

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

Posted on 2011-03-15
9
Medium Priority
?
353 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
[X]
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
  • 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 2000 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 58

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 58

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 58

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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