Need help writing a query

[Eva]

Hi,

I need some help on writing a query.
I have got 3 tables. (Booking, CaravanBooking,
RoomBooking)

I want a query to retrieve details from all 3 tables.
the joining key is BookingID.
The problem i am having is that a person making a booking
may only book a caravan and not a room and therefore when
i join the 3 tables i do not get any results even if they
have a caravan booking (as the bookingID does not exist
in the roomBookings table).

Similarly they may book a room and not a caravan or they
may book both.

How can i write a query that will take this into
consideration??

 

[Tom Ellison]

Dear Eva:

As you are working on this query in the design grid, there are lines
connecting the tables that list the columns forming each JOIN. In the
window where you set these up (or they may be already set up from your
relationships) there are 3 choices. The first choice is the default,
and is called an INNER JOIN. This functions as you have descirbed
your query is working now.

To get to this, right click on the line between Booking and
CaravanBooking.

The other choices provide a way for you to create an alternate type of
JOIN. You will want a LEFT JOIN from Booking to CaravanBooking (as I
best understand you). It doesn't say LEFT JOIN however. Look for the
one that says "Include all rows from Booking and only those rows from
CaravanBooking" (or something very close to that). Select that one,
and repeat for the RoomBooking JOIN.

I think that should do it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts