Combining queries

[Schillers]

I am trying to create a booking system and now I have 2 tables. One containing caravans that are held on a site and another using the caravan number as a foreign key containing booking information such as dates booked and for which customer. So far I have a query that runs to make sure no caravans are double booked by displaying the available caravans. It looks like this:


SELECT TblCaravan.[Caravan No]
FROM TblCaravan INNER JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((TblCaravanBooking.[When Required])>Forms!Booking.When_Required And (TblCaravanBooking.[When Required])>Forms!Booking.Required_Until)) Or (((TblCaravanBooking.[Required Until])<Forms!Booking.When_Required And (TblCaravanBooking.[Required Until])<Forms!Booking.Required_Until))
ORDER BY TblCaravan.[Caravan No];

This works but as you can tell it would only show the ones that already have bookings made. How can I show the caravans that are available but don't already have bookings so don't show in TblCaravanBooking? Please help. This needs to be within the same query?

 

[stopher]

Try this:

SELECT TblCaravan.[Caravan No]
FROM TblCaravan LEFT JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((nz([When Required]>[Forms]![Booking].[Required_Until],True) Or nz([Required Until]<[Forms]![Booking].[When_Required],True))=True))

hth
Chris

 

[Schillers]

I literally cannot explain how much that helps!

Thank you!

 

[Schillers]

One more thing. How can I combine 2 queries that consist of:



SELECT TblCaravan.[Caravan No]
FROM TblCaravan
WHERE (((TblCaravan.[No Of Berth])=Forms!Booking![Berth Combo]) And ((TblCaravan.[Caravan Type])=Forms!Booking!Type));


SELECT TblCaravan.[Caravan No]
FROM TblCaravan LEFT JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((nz([When Required]>[Forms]![Booking].[Required_Until],True) Or nz([Required Until]<[Forms]![Booking].[When_Required],True))=True));




Is this possible?