COUNT And IFF Statement

[bash]

I have the following query, and am counting the total number of bookings and need to check against the capacity to ensure that I do not overbook.
On any session(tblSessions table) the total number of bookings should be <= to the capacity(tblVenues table).

Now the query below shows the total bookings per SessionID.

I need to select all the sessions from my table which have less bookings than the capacity.

Do I need to do something like IIF(Count(tblBookings.BookingID)) <= tblVenues.Capacity then select this SessionID in my select statement.


SELECT Count(tblBookings.BookingID), tblSessions.SessionID
FROM (tblVenues INNER JOIN tblSessions ON tblVenues.VenueID = tblSessions.VenueID) INNER JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID
GROUP BY tblSessions.SessionID




Please can you help.

 

[MSAccessRookie]

I have the following query, and am counting the total number of bookings and need to check against the capacity to ensure that I do not overbook.
On any session(tblSessions table) the total number of bookings should be <= to the capacity(tblVenues table).

Now the query below shows the total bookings per SessionID.

I need to select all the sessions from my table which have less bookings than the capacity.

Do I need to do something like IIF(Count(tblBookings.BookingID)) <= tblVenues.Capacity then select this SessionID in my select statement.


SELECT Count(tblBookings.BookingID), tblSessions.SessionID
FROM (tblVenues INNER JOIN tblSessions ON tblVenues.VenueID = tblSessions.VenueID) INNER JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID
GROUP BY tblSessions.SessionID


Please can you help.

Try using a HAVING Statement as part of the GROUP BY. Something like this might work:

GROUP BY tblSessions.SessionID HAVING Count(tblBookings.BookingID) <= tblVenues.Capacity

 

[bash]

New SQL

OK Thank you.

But what will the New SQL be, because when I do:


SELECT Count(tblBookings.BookingID), tblSessions.SessionID
FROM (tblVenues INNER JOIN tblSessions ON tblVenues.VenueID = tblSessions.VenueID) INNER JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID
GROUP BY tblSessions.SessionID HAVING Count(tblBookings.BookingID) <= tblVenues.Capacity

It says :

You tried to execute a query that does not include the specified expression Count(tblBookings.BookingID) <= tblVenues.Capacity as part of an aggregare function.

 

[namliam]

SELECT Count(tblBookings.BookingID), tblSessions.SessionID
FROM (     tblVenues 
INNER JOIN tblSessions ON tblVenues.VenueID     = tblSessions.VenueID) 
INNER JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID
GROUP BY   tblSessions.SessionID 
HAVING Count(tblBookings.BookingID) <= Max(tblVenues.Capacity)

Try that.