Ranking search results from a database

[big man]

I've recently found a technique for weighting search results from an SQL database (points allocated for how many times the term is found in each result using wordcount, more points for having the term in the name etc - stored in a temporary database table)

does anyone know of a way of creating something like this in access using queries, or will i have to resort to returning all the data to my page and then manipulating and storing it in session (i'd rather not do this as i have 85,000 products to search !)

cheers,

big man

[/dev/null]

Can you not return in your initial query just the items that contain the search term and then after this allocate the points?

Something like:

SELECT * FROM myTable WHERE description LIKE %searchterm%;

Then go through those results allocating a points weighting to each then just query the data back again , ordering by points.

[big man]

Cheers for the reply mate.

Yeah, i can do that, i was just wondering if it's possible to do it within access itself (which i'm now 99%certain it's not). The problem here is the quantity of data returned - there's up to 85,000 products in the database !

I'm trying to push the company to use an sql database and make my life easier, but they don't look like they're going to go for it at the moment

[/dev/null]

Ah - misread the post and thought you were using SQL initially. Sorry - I'll have a think though and see if there is a possible way!

[big man]

the company decided not to use a ranked result in the end, but i thought i'd have another crack at it to see if i could do it, and i've managed to come up with a reasonable, relatively fast solution (using asp.net)

SQL = 

SELECT DISTINCT(field1), field2 FROM mytable WHERE field1 LIKE %searchterm% or field2 LIKE %searchterm%

then dump the results into a dataset and work through the results like so:

    Public Function SearchRank(ByVal ds As DataSet, ByVal str As String) As DataView
        Dim dc As New DataColumn("rank", GetType(Integer))
        ds.Tables(0).Columns.Add(dc)

        str &= " "

        Dim i As Integer
        For i = 0 To ds.Tables(0).Rows.Count - 1
            Dim search As String = ds.Tables(0).Rows(i)("prod_short")
            Dim occur As Integer = 0
            occur += 3 * ((Len(search) - Len(LCase(search).Replace(LCase(str), "*"))) / Len(str))

            search = ds.Tables(0).Rows(i)("prod_long")
            occur += (Len(search) - Len(LCase(search).Replace(LCase(str), "*"))) / Len(str)

            ds.Tables(0).Rows(i)("rank") = occur
        Next

        Dim dv As DataView = ds.Tables(0).DefaultView
        dv.Sort = "rank DESC, prod_id"

        Return dv
    End Function

it works through each field and works out how many times the searchterm is present and then multipilies the result to give a weighting (ie it get's more points if the searchterm is found in the title) and then orders the results according to the weighting

i've just tried it out on a page that returns over 2,500 records, and it returned the sorted results and output to the page in 4 seconds and returned a paged version of 20 records in 1.1 seconds (this was running from a testing server, not a local machine)

i didn't think that was too bad a result in the end. obviously sql would have been faster and preferable, but consindering the size of the database i'm quite pleased with it