Query to count text occurrences and find top 10 or 20

[julsue13]

I have a table of data including a field defining name and path for defined users.

I would like to count the occurrence of each text name/path in the field, and determine the top 10 or 20 occurrences. Then create a table of the top occurrences with all table fields included.

Also, my current platform is Access 2000.

Any suggestions?

 

[Access Hero]

In Access help, look up:
Count()
group by
order by
top

 

[julsue13]

Thank you for the response. I have a good idea of what I would like to do regarding the (Count, group by, order by, top) functions... but unfortunately am having issues figuring out the proper syntax. I have programming and SQL background, but unfortunately Access 2000 requires particular synatax which I do not have background in.

The text field with occurrences I would like to quantify is "target_host_name_path" and the Table is "Results"

A similar suggestion for a numeric version of my issue was:

Select AlphaField, Count(*) As CountOfNumericFields
From YourTable
Group By AlphaField;

Or if you want a count of Each numeric value:

Select AlphaField, NumericField, Count(*) as CountOfNumericFields
From YourTable
Group By AlphaField, NumericField;

I've tried a similar phrasing (with text instead of numeric) but keep having syntax errors occur. Does anyone know the proper syntax in Access 2000 to first quantify the number of occurrences in a field and then find the top ten occurrences?

I've been handed a random assignment and really haven't done anything like this since college.

Any help is appreciated. Thanks!

Julia

 

[Brianwarnock]

Basically it will look like this

Select Top 20 count(Fldname) as CountofFldname
From tablename
Groupby Fldname
Orderby CountOfFldname Desc

Brian

acyually the Orderby might have to be
count(Fldname) desc

 

[julsue13]

Okay, so I am currently putting all the fields into a query, then in the target_host_name_path field "criteria" I am adding the code:

Select Top 20 count ( [Results]![target_host_name_path] ) as CountofFldname
From Results
Groupby [Results]![target_host_name_path]
Orderby CountofFldname Desc

Error message I keep getting: "the sytnax of the subquery in this expression in incorrect"

Am I trying to apply the code in the wrong location Or is my syntax just incorrect?

Thanks,

Julia

 

[Access Hero]

Cool thing about using Access is that you don't have to know code. Just use the built in Query Builder tool. The instructions for using it are in help and the topics I mentioned before are well supported in the Query Builder (aka QBE).

Then to see what happened, just look at the SQL view.

 

[Brianwarnock]

Not tried it as a subquery, however this

Orderby CountofFldname Desc

shouldbe
Orderby count ( [Results]![target_host_name_path] ) Desc

I did correct my original post well sort of, aircode can be errorprone as it is not tested.

Brian

Edit just realised that you also have spaces were you shouldn't this should be
count([Results]![target_host_name_path]) for example

 

[julsue13]

Okay, so I finally figured out to just set this up in the SQL view...

SELECT TOP 20 Count(Results.target_host_name_path) AS CountofFldname,
FROM Results
GROUP BY Results.target_host_name_path,
ORDER BY CountOfFldname DESC;

In the above code, does anyone know what parameters would apply to CountofFldname?

And also, what is the best way to make sure all field names (from the original table) show in the top ten list?

Thanks!

Julia

 

[Access Hero]

In the above code, does anyone know what parameters would apply to CountofFldname?

I don't understand the question.

And also, what is the best way to make sure all field names (from the original table) show in the top ten list?

Put them in the select and group by clauses separated by commas.

select top 20 field1, field2, fieldetc, target_host_name_path, count(*)
from Results
group by field1, field2, fieldetc, target_host_name_path
order by 5 Desc

 

[julsue13]

Thanks guys - I guess that saying the truth lies somewhere in between really applies!

Finally got it