PDA

View Full Version : Access - sort by occurrence



Morgenmuffel
11-05-2005, 11:17 AM
Hi all
I have a table in access that i need to sort by occurrence, I'm sure i've asked it before here (and got a response but I can't find it in the archives, so possibly i didn't), anyway help would be appreciated

Thanks

andrew93
11-05-2005, 11:21 AM
Hi Nigel
If you are looking at the data in the Access table, right click the 'occurence' field and then click sort ascending (or descending). If you are trying to sort/view the data in a query, form or report, let me know.
HTH, Andrew :)

Morgenmuffel
11-05-2005, 12:05 PM
Currently its all in a table in access, no queries anything like that yet



<table>
<tr><td>title</td><td>search term</td><td>url</td></tr>
<tr><td>Blue Widgets</td><td>blue widgets</td><td>www.me.com/bluewidget.htm</td></tr>
<tr><td>Blue Widgets</td><td>widgets </td><td>www.me.com/bluewidget.htm</td></tr>
<tr><td>Blue Widgets</td><td>blue </td><td>www.me.com/bluewidget.htm</td></tr>
<tr><td>red thingies</td><td>thingies</td><td>www.me.com/redthingies.htm</td></tr>
<tr><td>red thingies</td><td>red thingies</td><td>www.me.com/redthingies.htm</td></tr>
<tr><td>red thingies</td><td>red </td><td>www.me.com/redthingies.htm</td></tr>
<tr><td>orange oranges</td><td>orange oranges</td><td>www.me.com/oranges.htm</td></tr>

</table>

this is a an example of the table, what i want to do is sort the table by how often the title occurs

I can do it in excell fairly easily so if it is too difficult I can always export the data sort it then reimport it, but I would prefer to stick to access for this

The final report will be in excell format, but access makes some things a little easier

Ok so the html didn't work but hopefully you get the idea

andrew93
11-05-2005, 01:20 PM
To count and sort records in the table you will need to create a query.

Following is some sample SQL to help with the count and sort on a table that I called 'Nigel' which had a field called 'title' :

SELECT Count(Nigel.title) AS CountOftitle, Nigel.title
FROM Nigel
GROUP BY Nigel.title
ORDER BY Count(Nigel.title) DESC;

This query counts the instances or occurences of 'title' and sorts them in descending order.

If you want to add more fields (like search term per your example, you can't do this in the first query) then you will need a second query which is based on the original table and the first query (that I saved as qryNigel1). Here is the SQL for the 2nd query :

SELECT qryNigel1.CountOftitle, qryNigel1.title, Nigel.[search term]
FROM Nigel INNER JOIN qryNigel1 ON Nigel.title = qryNigel1.title;

This query starts with the first query but then includes additional fields from the table.

You can modify these to suit your situation but these were based on the info per your example. You can get into the SQL design screen when in query design mode click on View -> SQL View. You can then revert back to the normal view by clicking View -> Design View.

Remember to modify the field and table names to suit your situation.

HTH, Andrew. :)

Morgenmuffel
11-05-2005, 02:07 PM
Thanks Andrew :thumbs:
that solves the problem 100% and I can actually understand whats going on