Sorry for the struggles.
Here is exactly what I did using Access 2003...
I created a new query and selected my table (in my case Table1). I selected two fields (in my case Field1 and Field2). I changed the query to show totals and set "Field1" to "Group By" and "Field2" to "Count". I made the name of the "Field2" "CountOfName" by adding "CountOfName: " before "Field2". I saved the query as "qryCount" and tested it to see if I was getting the correct grouping and counting.
I then added a field to the query
I changed the Total setting for that to "Expression" (even though it can be left as "Group BY").
I ran the query. It worked. I saved the query and ran it again. It worked.
I also did this in Access 2000. Everything worked fine for me.
The trouble is I don't know exactly what you did to use the SQL statement sample I sent and you didn't indicate which version of Access you're using.
For example, did you copy my example, change the names and save the query as "qryCount"? If you haven't saved the query, the DCount function won't work. But the error I get if I try not saving the query first is that "qryCount" isn't found.
When I copy your SQL statement to a new query in my database, the error I get is "blah, blah... cannot find table tblDupCities".
The point is, I can't seem to reproduce the error you're getting.
Is the select statement you put in your post a copy from your query or did you retype it? If you retyped it you may have corrected something, e.g. "FROM" instead of "FORM" or some such. By the way, I tried this little typo too. Once again the error is "table not found"
Is the name of your table actually "tblDupCities"? Or is tblDupCities actually another query?
One last thought is that your database is corrupt. Try a compact and repair. Try relinking tables (in case tblDupCities is a linked table). As a last resort, try creating a new, very simple database and try the solution I proposed in that simple database. If that doesn't work, somebody is going to tell me how to reproduce your error so I can debug it more.
Sorry I can't think of any other way to be of more help.
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group