Problem with SQL Query on ColdFusion Web Page

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.
kenjpeteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try in access

select [columnName], count([columnName])
from table
group by [columnName]
Jeffrey CoachmanMIS LiasonCommented:
<no Points wanted>
The SQL above to can also be modified display the top(n) values as well.
Or even all the vales that appear more than(n) times

I am sure Rey can help you with the details...
kenjpeteAuthor Commented:
Thank you, that worked, in that I was able to pull all the unique keywords in that table.  Now I would like to rank them by the most entered and provide a count of each.

In my research on the COUNT function it suggests using the AS feature, but I'm also wondering how to display that total count number. Would I use SUM or another function for that?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

kenjpeteAuthor Commented:
I tried the following and when I dump the recordset it appears to work but I keep getting a syntax error in my ORDER BY clause?

 
SELECT Keywords, COUNT (Keywords) AS TotalKeywords
  FROM TBL_Stats
  GROUP BY Keywords
  ORDER BY TotalKeywords DESC
Rey Obrero (Capricorn1)Commented:
SELECT Keywords, COUNT (Keywords) AS TotalKeywords
  FROM TBL_Stats
  GROUP BY Keywords
  ORDER BY Keywords DESC
Rey Obrero (Capricorn1)Commented:
oops

SELECT Keywords, COUNT (Keywords) AS TotalKeywords
  FROM TBL_Stats
  GROUP BY Keywords
  ORDER BY COUNT (Keywords) DESC

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenjpeteAuthor Commented:
Thats perfect! Thanks Rey....I'm all set now!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.