I had the same problem and found it easier to create the query and test it outside of the recordset/dreamweaver. I am new to MySQL, but I have found SQLyog (www.webyog.com) a really useful tool for this and there is a 30 day free trial, which I am using (but may end up actually getting the program as it saves so much time and typing). Their QueryBuilder is a very similar format to Microsoft Access if you have ever used that.
Once I have it working in SQLyog, I copy and paste the query into my recordset. Oh, but don't forget to remove the ";" from the end of the query in SQLyog as it really mucks things up if you keep it in there (took me ages to figure out why this was happening).
Hope this is of some help.