A more sensible thing you can do is solder a switch in series with the 12 volt line for the case fans (not the processor fan. That would be stupid). The turbo switch is a good one for this, as it's on the front panel and doesn't do anything anymore. Having done this, you can just switch off any superfluous fans while the machine is idling, and switch them on again when you're playing quake. Just make sure you're using lots of insulating tape, or the magic smoke might get out. For an automatic solution, thermaltake produce the 'smart fan II', which is the fan from the volcano 9 heatsink. It has a jumper point to which a (bundled) rheostat or temperature sensor can be attached. It could be easily wired to the turbo switch for manual control.

The loudest thing in your PC is the hard drive, not the fans (unsurprising when you consider that it's a large bit of metal spinning at over 5000 rpm with an arm seeking back and forth over it at 50 km/h). Setting the spin-down time to a minute or so will quieten things down. Sticking rubber washers between it and the case can help if the case is vibrating with the hard drive, although some drives have these built in.

Despite being a cheap bastard, there's nothing worse than a cheap processor fan. The bearings make noise, the air going through the vanes makes noise, and really bad ones vibrate. So don't skimp on your processor fan.

I highly recommend the zalman 'flower cooler' range of heatsinks. They come with a seperate fan, which is attached to the card-slots by a gigantic bracket. The fan is huge but slow turning, passing the same amount of air as a conventional heatsink. As it isn't attached to the heatsink, it can't pass vibration on to it.