Search code examples
ms-access

logical calculation within Access Query

I have a Access DB table that have the following data.

Room-Bed Status
100-A Available
100-B Occupied
101-A Available
101-B Available
102-A Occupied
102-B Occupied

Having the room logic calculation as follows

  1. In one bed, there are two beds, if any one bed is occupied, the room status is occupied.
  2. If both beds are not occupied, the room status is available
  3. If both beds are occupied, the room status is occupied
  4. Likewise, is there are rooms with two beds or more, the room status is occupied as long as one of the bed is occupied.

Is there a way to design a query such that it will computed a room-level result as follows

Room Status
100 Occupied
101 Available
102 Occupied
Solution

  • Try this:

    Select 
    CStr(Val([Room-Bed])) As Room,
    Max([Status]) As RoomStatus
From
    Rooms
Group By
    CStr(Val([Room-Bed]))

    Result:

    Room RoomStatus
    100 Occupied
    101 Available
    102 Occupied