This is my favorite one that I've done and I have a Canon Powershot A80. Its a 4.0 megapixel and will print almost perfect picture quality if not better. It is fully automatic OR manual settings, which will be nice when I learn how to use them. It also has a really neat screen that tilts to any angle so you can stick both arms in the tank and see the screen from any angle.
IF you view it as a full size picture you can see the quality. I was about 2" away from her when I got this one. The camera has a great macro lens. I dont really remember the cost of it, but I think it was about $379 or something like that. You can probably get them cheaper now. [/list]
I have moved to a DSLR but unless you are planning on spending $2000+ it may not be the best option for everyone. If you have past experience with film or want to learn a DSLR then by all means go for it.
There are a number of pictures on my site from both the Sony F707 and the Canon Digital Rebel, if you look at the full size you can see which camera it was from at the bottom:
I will second Kyles recommendation on the sony L style cybershots. I have the f717 and will probably go for the 828 soon. Macro shots are very simple and awesome. I recommend getting some closeup lenses for shooting frogs. It allows you to take a really tight shot without being 1" from the subject. Those lenses also seem to help me as I don't have to bury down so far in the tank, when I do my lenses fogs up pretty easily.
I would say those are your best choice for a point and shoot, if you want an SLR thats a whole new story (then again you probably wouldn't ask if you were in the market for an SLR)