Need help with improving my databasequery.

[Tallman]

I have the following MS Access table:
Name -- Content
Attributes -- Title
Shortdesc
Longdesc
Synoniemen

Using the next query I retrieve records from the table.
SELECT Content.Title, Content.ShortDesc, Content.Longdesc,
Content.synoniemen FROM Content WHERE ((Content.synoniemen LIKE
'%Blah%') OR (Content.Title LIKE '%Blah%') OR (Content.ShortDesc LIKE
'%Blah%') OR (Content.longdesc LIKE '%Blah%')) ORDER BY Title;

This query gives me all the records I need but I like to present them
in a specific order.
If the searchword is found in the TITLE field it is more important
then when it is found in the SYNONIEMEN field. The SYNONIEMEN field is
more important than the SHORTDESC field etc.

Is there a way to present the results in the desired order
(Title-Synoniemen-Shortdesc-Longdesc) ?

Thanx in advance.
Tallman

[Aaron Bertrand - MVP]

Could you do a UNION?


SELECT Title, ShortDesc, LongDesc, Synoniemen, [rank] = 1
FROM Content
WHERE Title LIKE '%BLAH%'
UNION
SELECT Title, ShortDesc, LongDesc, Synoniemen, [rank] = 2
FROM Content
WHERE Synoniemen LIKE '%BLAH%'
UNION
SELECT Title, ShortDesc, LongDesc, Synoniemen, [rank] = 3
FROM Content
WHERE ShortDesc LIKE '%BLAH%'
UNION
SELECT Title, ShortDesc, LongDesc, Synoniemen, [rank] = 4
FROM Content
WHERE LongDesc LIKE '%BLAH%'
ORDER BY rank, title


In SQL Server, you could use a CASE expression in the ORDER BY clause...





"Tallman" <tallman@tlzone.zzn.com> wrote in message
news:3b606e0a.0310150432.6017d977@posting.google.c om...

> I have the following MS Access table:
> Name -- Content
> Attributes -- Title
> Shortdesc
> Longdesc
> Synoniemen
>
> Using the next query I retrieve records from the table.
> SELECT Content.Title, Content.ShortDesc, Content.Longdesc,
> Content.synoniemen FROM Content WHERE ((Content.synoniemen LIKE
> '%Blah%') OR (Content.Title LIKE '%Blah%') OR (Content.ShortDesc LIKE
> '%Blah%') OR (Content.longdesc LIKE '%Blah%')) ORDER BY Title;
>
> This query gives me all the records I need but I like to present them
> in a specific order.
> If the searchword is found in the TITLE field it is more important
> then when it is found in the SYNONIEMEN field. The SYNONIEMEN field is
> more important than the SHORTDESC field etc.
>
> Is there a way to present the results in the desired order
> (Title-Synoniemen-Shortdesc-Longdesc) ?
>
> Thanx in advance.
> Tallman