thank you thank you for all the replies, it just shows me how many different ways there are to accomplish the same thing 🙂

Thank you for the quick reply 🙂 SUM (CASE...) was what i was looking for and works like a charm, thanks again!

If anyone else is having this issue its due to a bug in SP3 (SP2 works fine). Bug report posted on Connect.

certainly something in SP3 which specifically stops you from accessing datasources that have spaces in the table name.

Its something in SQL2005 Service Pack 3 causing the problem. I've installed a clean system in VMWARE and updated and tested all the way through to SP2 and everything works...