Microsoft Access SQL Query - instances

Question

I want to find modules that have more than two room bookings (i.e., have two instances in the table) and show the number of bookings.

In my roomBooking table there's

 bookingID | roomCode | moduleCode | dayReq | timeReq | semester | classSize 
    1      |  AM200   |  CT230     |  Tues  | 14:00   |     1    |    130
   10      |  AM200   |  ST237     |  Tues  
   11      |  SC005   |  EC273     |  Mon   
   12      |  AM150   |  EC273     |  Tues  
    2      |  AM200   |  CT230     |  Wed
    3      |  IT250   |  CT229     |  Fri
    4      |  SC005   |  CT2103    |  Mon
    5      |  AM200   |  ST237     |  Mon
    6      |  IT125G  |  CT2101    |  Mon
    7      |  IT204   |  CT2101    |  Tues
    8      |  AC216   |  CT2103    |  Fri
    9      |  UC102   |  CT229     |  Fri
   13      |  IT204   |  CT335     |  Mon
   14      |  SC002   |  CT335     |  Wed

ect.

I'm unsure as to how to create a query that asks for instances within the table, for example, moduleCode CT230 could be associated with bookingID 4 7 and 13 but how do you show the instances and apply that to a SQL Query?

Answer 1

From the details in your question I think this is what you're looking for:

SELECT 
    COUNT(BookingID) AS 'Number of bookings'
    , ModuleCode
FROM roomBooking
GROUP BY ModuleCode
HAVING COUNT(BookingID) > 2

Update:

It is possible that you were actually looking for modules that have at least two room bookings, instead of "more than two room bookings" (quote from your question).

Using the sample data you provided, it is true that the above query will not return any results (but it works correctly, as per your question's request).

In case you were looking for modules that have at least two room bookings, then the query below will return data.

SELECT 
    COUNT(BookingID) AS 'Number of bookings'
    , ModuleCode
FROM roomBooking
GROUP BY ModuleCode
HAVING COUNT(BookingID) >= 2

This query is also supported by a SQLFiddle, based on the sample data you provided.