The Riad hotel is definitely the place to stay in Hamah. Its competition, the Cairo hotel, which is right next door, is pretty much the same (especially since the owner is the same!!!) except the people (the manager, Abdullah is fantastic) at the Riad make it all that. Even the owner's son likes to hang out at the Riad. Abdullah arranges trips to all of the main destinations within a possible day trip. The trips are based per car (or van), not per person, so if there are more people, the trip is cheaper.
You CAN get a close up look of the 4 norias. Just ask the guys at the 4 Norias restaurant and if they are in a good mood, they'll let you into the back area where you can jump the fence. :)