• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

What is the best way to write this SQL statement?

Ok.

So I have a project I am working on in Access. I have three tables that I need to grab data from, Bookings, Employees,Customers.

Bookings' records just store information about that booking, the employee that booked it (.BookedBy), and the customer that the booking is for.


I need to write a SQL statement that grabs all bookings "bookedBy" X that have a "DateOfJob"  between Y and Z.

This is not working:

SELECT Bookings.DateOfJob,Employees.FirstName,Customers.FirstName,Customers.LastName
FROM Customers,Employees,Bookings
WHERE Customers.CustomerID = Bookings.CustomerID
AND Employees.EmployeeID=Bookings.BookedBy
AND Bookings.BookedBy=<X>
AND Bookings.DateOfJob BETWEEN <Y> AND <Z>

X = the EmployeeID number of the person who booked the Booking
Y = the start date that the booking is between
Z = the end date that the booking is between


I know it involves JOIN. But im not really understand how to JOIN three tables.

Help!
0
bkaplunovskiy
Asked:
bkaplunovskiy
3 Solutions
 
bluelizardCommented:
since this in access, i have this suggestion:

set up your query with the normal query builder (use the mouse to connect fields, type in the WHERE-criteria etc.), and then switch to "SQL view" (menu view > SQL view) to see what the SQL code looks like.  it will create all the right "joins" in the right places for you...


--bluelizard
0
 
JestersGrindCommented:
Try this:

Greg



SELECT Bookings.DateOfJob,Employees.FirstName,Customers.FirstName,Customers.LastName
FROM Customers INNER JOIN
	   Employees ON Customers.CustomerID = Employees.CustomerID INNER JOIN 
	   Bookings ON Employees.EmployeeID = Bookings.BookedBy
WHERE Bookings.BookedBy=<X>
AND Bookings.DateOfJob BETWEEN <Y> AND <Z>

Open in new window

0
 
dan_nealCommented:
Or just do this in the SQL view:
SELECT Bookings.DateOfJob,Employees.FirstName,Customers.FirstName,Customers.LastName
FROM Bookings inner join Customers on Bookings.custmerid = customers.customerid inner join Employees on bookings.bookedby = employees.employeeid
WHERE Bookings.BookedBy=<X>
AND Bookings.DateOfJob BETWEEN <Y> AND <Z>
To join tables in a sql query, one must know what fields are related between those tables and then create a link/join where those fields are equal, not equal, greater than, less than, and so on.  What may be best is to start with just the fields and linked tables to see if your returning any data and if so is it the type of data that your expecting.  Once you have that down, then concentrate on the where clause to limit the data to just what you want.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now