I hate MS Access -> Garbled text in results.

[peterfelts]

Hi, I'm coming from a MySQL background and I'm having problems with MS Access.

I'm using a VC++.NET app to query Access and have been running into problems so I decided to try my queries from Access directly.

My questions are:
1) Is there a way to simply select all fields in a table and do a group by:

SELECT Product.*, COUNT(*) FROM `Product` GROUP BY Product.[Prdouct reference]

This is easily done in MySQL but it looks like I have to type every field I want in the result when I do a function such as COUNT(*)?

What's up with that?

2) When I limit the columns in my result with something like this:

SELECT Product.[Product reference], Count(*) AS num_duplicates, Product.[Short description]
FROM Product RIGHT JOIN Product AS leftjoin ON Product.[Product reference] = leftjoin.sOriginalProdRef
GROUP BY Product.[Product reference], Product.[Short description];

I get garbled text in the `Short description` field for some reason. Does anyone know why this is happening?

thanks everyone.

[olivthill]

Access' SQL is not the most comfrotable SQL there is, but one gets used to it after a few years of pratice.

1) Is there a way to simply select all fields in a table and do a group by:

My version of Access doesn't accept SELECT *, COUNT(*) either.

SELECT Product.[Product reference], Count(*) AS num_duplicates, Product.[Short description]
FROM Product RIGHT JOIN Product AS leftjoin ON Product.[Product reference] = leftjoin.sOriginalProdRef
GROUP BY Product.[Product reference], Product.[Short description];

I get garbled text in the `Short description` field for some reason. Does anyone know why this is happening?

I have tried your query and it is working well. Sometimes, [Short description] is empty, which is normal when sOriginalProdRef doesn't exist in Product reference. Perhaps, there are other cases that I don't see with my sample.