Can you make my SQL do what I want? Please!

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

I have the following query

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.Day, AVAILABILITY.BookingID
WHERE (AVAILABILITY.BookingDate >= StartDate AND
AVAILABILITY.BookingDate <= EndtDate AND AVAILABILITY.Day
= DayNumber)

which returns all the records for a particular day of the
week between 2 dates. I'm an SQL novice so I was pleased I
got this to work!

However what I really want to do is check the BookingID
for each record returned by the above query and if it is 1
for every record returned then take action 1 else take
action 2 (output a message saying not all dates available)

Can I do this with SQL or do I have to write some code?

(It's a bit like executing the above SQL statement,
getting a record count, then executing another SQL
statement as aove but with an added AND BookingID = 1 and
getting this count, then comparing the counts, if equal
action 1 else action 2. So is there an SQL construct that
counts matching records and puts them in a variable?)

Thanks
 
You can use the following query

SELECT Count(*) As Tot, Sum(BookingID) As Booked FROM AVAILABILIT
WHERE (AVAILABILITY.BookingDate >= StartDate AND
AVAILABILITY.BookingDate <= EndtDate AND AVAILABILITY.Day
= DayNumber

Compare Tot and Booked values. If they are equal (and greater than zero), then ..

You can also do it with two function calls (in a button's event)

Sub Test(
Dim tot, booke

tot = DCount("*", "AVAILABILITY", "AVAILABILITY.BookingDate >= StartDate AND AVAILABILITY.BookingDate <= EndtDate AND AVAILABILITY.Day = DayNumber") 'All in one lin

If tot = 0 The
Msgbox "No bookings made
Exit Su
End I

booked = DCount("*", "AVAILABILITY", "AVAILABILITY.BookingDate >= StartDate AND AVAILABILITY.BookingDate <= EndtDate AND AVAILABILITY.Day = DayNumber AND BookingID = 1") 'All in one lin

If tot = booked AND The
Msgbox "All dates available
Els
Msgbox "All dates not available
End I

End Su

----- Kate wrote: ----

I have the following quer

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.Day, AVAILABILITY.BookingI
WHERE (AVAILABILITY.BookingDate >= StartDate AND
AVAILABILITY.BookingDate <= EndtDate AND AVAILABILITY.Day
= DayNumber

which returns all the records for a particular day of the
week between 2 dates. I'm an SQL novice so I was pleased I
got this to work

However what I really want to do is check the BookingID
for each record returned by the above query and if it is 1
for every record returned then take action 1 else take
action 2 (output a message saying not all dates available

Can I do this with SQL or do I have to write some code

(It's a bit like executing the above SQL statement,
getting a record count, then executing another SQL
statement as aove but with an added AND BookingID = 1 and
getting this count, then comparing the counts, if equal
action 1 else action 2. So is there an SQL construct that
counts matching records and puts them in a variable?)

Thank
 
Back
Top