What is the best way to write this SQL statement?

Posted on 2009-04-16
Last Modified: 2012-05-06

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.

Question by:bkaplunovskiy
    LVL 14

    Assisted Solution

    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...

    LVL 21

    Accepted Solution

    Try this:


    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

    LVL 9

    Assisted Solution

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now