WHERE CLAUSE in SELECT

[bash]

Hi Everyone,

I have the following SQL statement. The COUNT(b.BookingID) AS TB, gives me the number of bookings. I need to ensure that the number of bookings + PrebookCapacity is less than v.Capacity. I do this by:

COUNT(b.BookingID) AS TB + PrebookCapacity < v.Capacity in the WHERE CLAUSE. But when I run this in MS Access 2003, it says Enter Parameter Value for b.TB (It does not recognise b.TB as b.TB is not a table field). If I omit AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)), from the WHERE clause, it works fine, also giving me the TB.

As I need to do my check in the WHERE clause, how can I check to make sure that the COUNT(b.BookingID) AS TB + PrebookCapacity is < v.Capacity, without any problems.

SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity,
COUNT(b.BookingID) AS TB
FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q
WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID
AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0))
GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;

Thanks,

Bash.

 

[ajetrumpet]

As I need to do my check in the WHERE clause, how can I check to make sure that the COUNT(b.BookingID) AS TB + PrebookCapacity is < v.Capacity, without any problems.

There is part of the code that will cause an error...

SELECT CourseName, 
  Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy  hh:ss AM/PM") & 
    " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, 
      PrebookCapacity, v.Capacity, COUNT(b.BookingID) AS TB

FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q

WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND 
  b.SessionID = s.SessionID AND v.VenueID = s.VenueID
    AND (([color=red][b]b.TB <--- you are referencing "TB" as an original field, it is not... write [i][TB][/i] instead [/color][/b]+ 
      PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0))

GROUP BY CourseName,  Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy  hh:ss AM/PM") & 
  " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;

It's not a field, it is a temporary name assigned to a field that represents a function.

 

[bash]

Thank you very much for you reply.

I did what you told me, but it still request for a parameter. Here is the new SQL, please can you take it, modify it then post the correct SQL.

SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity,
COUNT(b.BookingID) AS [TB]
FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q
WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID
AND ((b.[TB] + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.[TB] < v.Capacity AND SessionStatus = 0))
GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;

Bash.

 

[pbaldy]

I think the problem is that the WHERE clause is evaluated before the SELECT clause, so it doesn't know what TB is yet. Try

...AND ((COUNT(b.BookingID) + PrebookCapacity < ...

 

[bash]

I tried that before, before does not work, says cannot have aggregate function in where clause.

 

[pbaldy]

Put it in a HAVING clause instead.

 

[ajetrumpet]

SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy  hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity,
COUNT(b.BookingID) AS [TB]
FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q
WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID
AND ((b.[TB] [color=red][size=4][b]<---[/color][/size][/b] + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.[TB] [color=red][size=4][b]<---[/color][/size][/b] < v.Capacity AND SessionStatus = 0))
GROUP BY CourseName,  Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy  hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;[/QUOTE]You forgot a couple of places.  Is it still giving the same parameter (b.[TB])???  Is that the ONLY parameter???

If I didn't specify before either; [b]b.[TB][/b] needs to be [B][I]completely [/I][/B]replaced by [b][TB][/b]

Also, Paul said something about the "Clause Order".  Yes, you should change it, because you have a function in your SELECT statement (COUNT = aggregate function).  "HAVING clause" must be used as a substitute to WHERE when working with aggregates.

The order of the clauses needs to be corrected as well....

[I]SELECT fields, FROM table(s), GROUP BY fields, HAVING criteria.[/I]

 

[bash]

TB was the only parameter, being requested.
How would I link the fields in the tables together, using a Having clause, instead of WHERE, i.e.

c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID

From what you say the new SQL becomes:

SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity,
COUNT(b.BookingID) AS [TB]
FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q
GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity
HAVING c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID
AND (([TB] + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR ([TB] < v.Capacity AND SessionStatus = 0));

This does not work. It says You tried to execute a query that does not include the specified expression 'c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID
AND (([TB] + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR ([TB] < v.Capacity AND SessionStatus = 0))' as part of an aggregate function.

If you can, please rewrite the SQL, to what you think it should be.

Bash.

 

[pbaldy]

The [TB] will not work. Try what I gave you in post 4 in the HAVING clause.