Update Table by counting items in another table

[Studly]

Hello,
Hope someone can help with this (rather long) question,

I have 2 tables (first column of each is the PK):

"TBL_RoomTypes" is the types of rooms in my hotel:
RoomType NumberOf
Single 2
Twin 1
Double 2

"TBL_Rooms" is the actual rooms:
Num RoomType RoomStatus
1 Single Vacant
2 Single Occupied
3 Twin Occupied
4 Double Occupied
5 Double Vacant

I'm trying to write a query to update TBL_RoomTypes with a count of how many of each roomtype are in TBL_Rooms (this will change sometimes, so I want a query to do the counting for me)

I can get a list of the room types (from table 1) and count each from table 2 with this SQL :

SELECT TBL_RoomTypes.RoomType, Count(TBL_Rooms.RoomType) AS CountOfRoomType
FROM TBL_RoomTypes LEFT JOIN TBL_Rooms ON TBL_RoomTypes.RoomType = TBL_Rooms.RoomType
GROUP BY TBL_RoomTypes.RoomType;


but I'm really stumped how to make the query update column "NumberOf" in the first table, by counting the records in the second table.

Can anyone help me?
Thanks

 

[PHV]

Why using a table to store derived/calculated values ?
Use a query instead:
SELECT RoomType, Count(*) AS NumberOf FROM TBL_Rooms GROUP BY RoomType

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886