select records having maximum count of a 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

In other words, I need to select the "Product" for each "Customer" which that particular Customer ordered most.

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

 

[fneily]

Using your example as the source, I created three queries.
The first query finds the count of each product for each customer. The SQL look like:
SELECT MaxTable.Customer, MaxTable.Product, Count(MaxTable.Product) AS CountOfProduct
FROM MaxTable
GROUP BY MaxTable.Customer, MaxTable.Product;

Using the above output, I have a query to find the Max counts for the customer. The SQL looks like:
SELECT Product_count_query.Customer, Max(Product_count_query.CountOfProduct) AS MaxOfCountOfProduct
FROM Product_count_query
GROUP BY Product_count_query.Customer;

Now I connect both outputs in a query to show Customer, Product and Count. The SQL looks like:
SELECT Product_count_query.Customer, Product_count_query.Product, Product_count_query.CountOfProduct
FROM Max_Customer_Count INNER JOIN Product_count_query ON (Max_Customer_Count.MaxOfCountOfProduct = Product_count_query.CountOfProduct) AND (Max_Customer_Count.Customer = Product_count_query.Customer);

This gives your answer. Notice two fields are connecting in the third query.
Then if you want, you can have the results of one customer on one line by using:
faq701-4233