Query help needed

[burnoutuk]

I need to make a query that displays pitches at a campsite that are not booked for a given date the user enters.

I have a tbl which holds pitch info tblPitch and a table which holds booking info tblBooking (see attachment)

tblBooking has the pitch number, start date and end date so if theres a date like 1/11/2003 - 7/11/2003

if the user enters a date on or between then i dont want that pitch to be displayed but all pitches that arnt booked to be shown...

im not sure if this makes much sense but if someone gets it please help im new to this.

Heres what ive been trying

SELECT DISTINCT [tblpitch].[siteNo], [tblpitch].[pitchNo]
FROM tblpitch, tblBooking
WHERE tblpitch.pitchNo = tblbooking.pitchNo

And tblBooking.bookingDate between [Enter Date]

and tblbooking.pitchNo NOT IN (select pitchNo from tblBooking)
ORDER BY [tblpitch].[siteNo];


but its wrong as i have no end date or anything.


Thank you for any help.

 

[AlienRay]

Step 1: create a query that shows all pitches that are booked on the date specified
Step 2: create a query that joins your master pitch list to Query1, looking for all items in the pitch list. Set the criteria for the pitch name in Query1 to "Is Null". (In other words, create an unmatched query)
Step 3: link your output to Query2

 

[burnoutuk]

I've tried that and follwed it as best i could but it returned no results at all

Thank you for the reply.

 

[FoFa]

How about something like:
Select Pitch_Number from tblPitch
Where Pitch_Number Not In
(select pitch_number from tblbooking where (RequestStart >= booking_date AND RequestStart <= BookingEnd) AND (RequestEnd >= Booking_Date AND RequestEnd <= BookingEnd))

 

[burnoutuk]

FoFa I tried that but it returns pitches which shouldnt be still but takes out someone which should be there

Ive included the basics from my database

 

[burnoutuk]

I just want to enter one date that date then returns pitches which are not booked between my booking date and booking end date in the bookings table.

Thank you for all the help.. sorry im not explaining very well.

 

[FoFa]

Using your example, this seems to do it:
SELECT tblBooking.bookingNo, tblBooking.bookingDate, tblBooking.pitchNo
FROM tblBooking
WHERE (((tblBooking.pitchNo) Not In (select pitchno from tblbooking where (bookingdate >= [Request Start] AND bookingdate <= [Request End]))));

But you only have one date in your booking file, so you have to specify two dates (start and end) to eleminate all bookings in between those dates, at least per the example you uploaded.

 

[burnoutuk]

I couldnt seem to get that working FoFa..

(The db included has a different booking table with end dates)

 

[FoFa]

This works with your newest example:
SELECT tblPitch.pitchNo, tblPitch.siteNo
FROM tblPitch
WHERE (((tblPitch.pitchNo) Not In (select pitchno from tblbooking where ([Request Date] >= bookingdate AND [Request Date] <= BookingEnddate))));