Help to develop more complex SQL select

  • 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?)

Thank you
 
You might try building a join from the booking table..and then use a group
by to sum a column you add called one:1

However, after you do get the records returned...you would have to process
the results one by one..and for that you do need code.

Not knowing your table structure makes answer this hard). I have to assume
the a booking ID does resolve to something?

Anyway, a basic processing loop in code looks like:


dim rstReocrds as dao.RecordSet

set rstRecords = currentdb.OpenReocrdSet("select * from tblCustomers where
city = 'Edmonton'").

do while rstReocrds.EOF = false
debug.print "wroking on reocrd id " & rstReocrds!ID
rstRecords.MoveNext
loop
rstReocrds.Close
set rstRecords = nothing

So, in the above loop..you can process each reocrd from the sql...

And, if you need some more ideas on writing a booking system..I have some
great notes here, and even show most of my table designs, and even some
objects I used:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000003.html
 
Back
Top