I have a ColdFusion web page with a keyword search box that allows users to search for information in our MS Access database. That same keyword box also inserts any searches done into a database table called TBL_stats.
I want to query that table and show the most popular keywords, along with a count of how many times a particular keyword/phrase has been searched on.
I am having trouble figuring out the SQL query on how to count the number of occurrences of the same keyword. Unfortunately using COUNT (DISTINCT column name) doesn't work in MS Access. Also, I'm not sure if it would be better to pull those keywords into a ColdFusion array and count them, or to try and return a SQL recordset with the necessary data.
I would appreciate any help to point me in the right direction.