Having had to maintain some reports in Access I can wholeheartedly agree
It's even worse when people without any knowledge of relational databases try to create queries in therer (but that is another story and doesn't belong here)
I don't think that query will run as posted. I think that the use of the reserved word COUNT as a column name will cause havok when SQL Server tries to figure out what you want it to do.
Based on my interpretation of your interpretation of SQL Server's interpretation of what you typed (from your rendition of the error message that SQL Server gave), it appears that SQL Server is trying to do grouping for you.
See one of the sticky mesages at the top of the forum for direction on how to get correct answers quickly. Brett wrote up a great short post on ths, and I stole his post wholesale in the FAQ (Frequnetly Asked Questions) thread.