select query choosing records having maximum number of instances of a specific field?

[Fekla]

Good day to you all,

I am looking for help in a query. Please help me write the SQL statement. Let's say I have a table with records like this:

Autonumber Customer Product
##### James Keyboard
##### James Monitor
##### James Keyboard

and I need to show for every customer the product he has selected most often (in this case, keyboard). I can't use the MAX function for "product" because it would show the product being MAX from ASKII point of view (like, "monitor" coz it starts with an M) , NOT the maximum number of instances.
Also, is there a way to concatenate entries, so that if there are 2 results (lets say a customer has bought 2 keyboards and 2 monitors) it is still displayed as one like
##### James Keyboard+Monitor

THank you in advance!

P.S. BTW, I DO know that my example table should be distributed into many tables. I just use an example because the actual table i m trying to perforn query on is just too complicated to explain

 

[John Big Booty]

Use the Totals Button (Greek letter Sigma) in the design view of the query, then put the product into the grid twice, on the first occurrence select Group by and then in the second occurrence select count. Then in the customer you can put the customer name in the Criteria to see the totals of the products that cusotmer has purchased.

 

[Fekla]

That would have been too simple! I m afraid that's NOT what I am looking for.
Your query would show the results as having 2 Keyboards for James and 1 Monitor, whereas I would like it to show only
James Keyboard.
Moreover, I don't need to use the criteria for Customers because as I said, I need this for EVERY Customer.
In other words, for every customer I want to show 1 product which he has purchased most of all.

 

[georgedwilkinson]

Then filter it for the word "Monitor"!

 

[Fekla]

I am sorry, but did you read what I have written? Or should I give up some more explanations?
I do not know for which word to filter, I need to select the "Product" for each "Customer" which that particular Customer ordered most.

 

[stopher]

Take a look at my example. It uses two queries. The first is like John Big Booty said. The second then uses the first and a subquery of the first to get the required result.

This may not be an optimal solution as I'm not that hot on subqueries.

hth
Chris