You have a Customer Field, Date Field, and Product Field
I have the query set up to select only recordes within a selected date range. Now I want to create a field which tells what product was most common out of the records the query came back with. I've sat here and sat here and just cannot think of a way to do this.
I show the product name (group-by) and the total# by product (count) in a query, and then sort descending by the count (so the highest count of any one product is at the top). Then I just set the property of the query to return only 1 record.
SELECT TOP 1 tblOrders.ProductName, Count(tblOrders.ProductName) AS TotalByProduct
GROUP BY tblOrders.ProductName
ORDER BY Count(tblOrders.ProductName) DESC;
I'm sure there are other ways (maybe better) to do this.