Carry out a search using keywords

[Dangermouse]

I am currently working on a new project, where as a business we have to quote
on baskets of products supplied to us by a customer. The issue I have is the
UOM (Unit of Measure) for pricing purpose’s, I have a table containing Key
words, eg, “Mtr†“Ft†“Ml†“Grm†etc…..

What I want to do is this using the table containing the key words, scan the
Part description field, held within another table, and flag that record, for
further analysis.

E.g. 110038X10FT:REN – in this example “FT†has been found

How do I go about this

Thanks

 

[Graham Mandeno]

Hi Dangermouse

I haven't tried this, but it should work...

Assuming your Parts table has fields named PartNum (primary key) and
PartDescr, and your Keywords table has a text field named Keyword:

Create a table named KeywordsFound with two fields:
PartNum - same data type as the PartNum PK in your Parts table
Keyword - text

Then create and run this query:

INSERT INTO KeywordsFound ( PartNum, Keyword )
SELECT Parts.PartNum, Keywords.Keyword
FROM Parts, Keywords
WHERE InStr([PartDescr],[Keyword])<>0;

You will then have a table containing the PKs of all the Parts whose
descriptions contain any of the keywords.

 

[Dangermouse]

Thankyou Graham

That works fantastic

Hi Dangermouse

I haven't tried this, but it should work...

Assuming your Parts table has fields named PartNum (primary key) and
PartDescr, and your Keywords table has a text field named Keyword:

Create a table named KeywordsFound with two fields:
PartNum - same data type as the PartNum PK in your Parts table
Keyword - text

Then create and run this query:

INSERT INTO KeywordsFound ( PartNum, Keyword )
SELECT Parts.PartNum, Keywords.Keyword
FROM Parts, Keywords
WHERE InStr([PartDescr],[Keyword])<>0;

You will then have a table containing the PKs of all the Parts whose
descriptions contain any of the keywords.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am currently working on a new project, where as a business we have to
quote
on baskets of products supplied to us by a customer. The issue I have is
the
UOM (Unit of Measure) for pricing purpose's, I have a table containing Key
words, eg, "Mtr" "Ft" "Ml" "Grm" etc...

What I want to do is this using the table containing the key words, scan
the
Part description field, held within another table, and flag that record,
for
further analysis.

E.g. 110038X10FT:REN - in this example "FT" has been found

How do I go about this

Thanks

 

[John Spencer]

A non-equi join query should give you the records

SELECT DISTINCT A.PartNumber, A.PartDescription
FROM PartsTable as A INNER JOIN KeyWordsTable as B
ON A.Description LIKE "*" & B.KeyWord & "*"

You cannot build this type of query using design view (the query grid) and you
cannot display it in design view. You can use the design view to get started.

In design view
-- Add both tables
-- Select the fields you want to see.
-- Click on Description and drag to Keyword to set up a join
-- Select View: SQL from the menu
-- Edit the join text from
ON PartsTable.Description = Keywords.KeyWord
to
ON PartsTable.Description LIKE "*" & Keywords.KeyWord & "*"
-- Save the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dangermouse]

Thanks John

just tried your solution works fine, as well