Yes, it should suck up the poo.
If you are using a thick layer of sand (which isn't reccomended) you should stir it up before every weekly partial water change to keep any deadly pockets of gas forming. Even with only about half an inch of sand, you should still stir it up to help rid it of extra debris.
Corydoras catfish, some other catfish, khulie loaches and certain snails will also adequately stir up sand if it isn't too deep.
Sand looks fantastic in a freshwater tank but you have to be careful to get the right kind for your tank.
For example, if you want bottom-dwelling catfish like cories you don't want anything sharp so sillica sand (for example) should be avoided. Also, crushed coral should be avoided in most tanks as it raises pH and acts as a buffer. It's useful in rift lake cichlid tanks though.
The rest is up to personal prefference.
Keep in mind that some sand is dustier than others and will need more washing and take longer to settle (a seriously cloudy tank for a couple of days isn't that rare...). Try to avoid this sort of sand as it'll also be more difficult to clean and won't settle if stirred up.
Dark sand will hide poo better than light sand where it is incredibly obvious and often calls for daily cleaning. A darker substrate also generaly brings out color more. It's up to your taste in the end but I don't like very bright yellow or orange sand and preffer either pale, almost white, sand or a dark brown-grey to black. But that's just me
You may put in rocks or whatever but I think pure sand looks best.
If you can get it, the kind of sand used for pools is ideal. Beach play sand and other sands are all generaly fine but remember to clean thouroughly first and be patient for the sand to settle.
Lastly, a search of this forum will bring up SO many threads concerning sand you can't imagine... so... you know what to do