tblSport( sport text, primary key(sport) )
sport
'Tennis'
'TableTennis'

tblCourt( courtnbr int, sport text, area text, primary key(courtnbr) )
-- you could use courtnbr+sport as primary key, depends on the requirements
courtnbr | sport |area
1, 'Tennis', 'indoor'
2, 'Tennis', 'outdoor'

-- put a foreign key between tblSport and tblCourt: tblCourt(sport) references tblSport(sport)

tblTime( time short time, primary key(time) )
time
8:00
9:00
10:00
11:00
…
20:00 

tblDate( date time, leisure_center_open boolean, primary key(date) )
date | leisure_center_open
20041101,true
20041102,true
20041103,false
20041104,true
...
-- put as many dates as you want here

tblBooking( courtnbr int, date time, time short time, fName text, sName text, comment text, primary key(courtnbr,date,time) )
courtnbr | date | time | fName | sName | comment
1, 20041101, 08:00, 'John', 'Wayne', ''
1, 20041101, 09:00, 'John', 'Wayne', ''
1, 20041101, 10:00, 'Leisure Center', '', 'Closed for maintenance'
1, 20041101, 11:00, 'Leisure Center', '', 'Closed for maintenance'

-- put a foreign key between tblBooking and tblCourt: tblBooking(courtnbr) references tblCourt(courtnbr)
-- put a foreign key between tblBooking and tblDate: tblBooking(date) references tblDate(date)
-- put a foreign key between tblBooking and tblTime: tblBooking(time) references tblTime(time)


Comment:
"the thing I cant do is on the click of a button for the list box to display all the available courts,
for each of these courts it has to show the court number,
the time that court is available,
and weather it is indoors or outdoors"
-- Query 3 below will get you that.

-- modifiy the following so that it fits the sql dialect of MS access.

-- query 1 will give all available times for all courts
-- it basically returns all the combinations of courts+dates+times filtered on date range
select
	tblCourt.courtnbr,
	tblCourt.area,
	tblDate.date,
	tblTime.time
from
	tblCourt
	,tblDate
	,tblTime
where
	tblDate.leisure_center_open = 'true'
	and tblDate.date between 'from_date' and 'to_date' -- here you have to specify the daterange

-- query 2 will give all booked times for all courts filtered on date range
select
	tblCourt.courtnbr,
	tblCourt.area,
	tblBooking.date,
	tblBooking.time,
	tblBooking.fName,
	tblBooking.sName
from
	tblCourt
	join tblBooking on tblCourt.courtnbr = tblBooking.courtnbr
where
	tblBooking.date between 'from_date' and 'to_date' -- here you have to specify the daterange

-- Query 3
-- If we combine query1 + query2 then we can show all the available times
select
	tblCourt.courtnbr,
	tblCourt.area,
	tblDate.date,
	tblTime.time
from
	tblCourt
	,tblDate
	,tblTime
	left join tblBooking
		on tblCourt.courtnbr = tblBooking.courtnbr
		and tblBooking.date = tblDate.date
		and tblBooking.time = tblTime.time
where
	tblDate.leisure_center_open = 'true'
	and tblDate.date between 'from_date' and 'to_date'
	-- and tblBooking.courtnbr is null -- this filter will show only the timeslots that are available

quote:

---

is it possible to combine the 2? thanks sam

---