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

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

CodePudding user response：

Try this:

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

Result:

CodePudding user response：

As far as I see it, the logic can be simplified to the following rule:

• For each room, if any of the beds are occupied, the room is occupied.
• Otherwise the room is available.

The logic below is to count, for each room, the number of beds occupied (done below by giving an occupied bed a value of 1, otherwise it's 0 - then take the SUM).

Note that the below assumes you have a way to make a table room_bed_list with the columns room, room-bed, and room-bed-status. This is similar to your original table, but adds a room column - I'm assuming you already have a link from room-bed to this in another table.

SELECT room_bed_status.room,
       IIF(Sum(IIf([room_bed_list].[room-bed-status] = "Occupied",1,0)) > 0, "Occupied", "Available") AS room_status
FROM room_bed_list
GROUP BY room_bed_status.room;