looking for suggestions on using keywords

[ntambomvu]

Hello Chaps-
I have a access table containing contact information.
Many suppliers are listed and each has his own range of products.

In my previous table (which was a dos based .dbf file ) i had a text field of indeterminate length
into which I manually types keywords about the suppliers offerings.

It worked worked this way- Apex hardware would have the following entry into his keywords
"hardware hammers chisels glue tools nails " (etc etc) when looking for a supplier I would then do a
keyword search the query being as follows ."List all contacts whose keyword field contains "hammer""


and I would get a list of possible suppliers.
I am looking for suggestions about how to implement a similar procedure into access and i would
be very grateful for any suggestions.
best regards

fred evans

[ranman256]

youd make a form with a textbox, txtFind.
then a button would open a query that looks at this textbox
the query sql would be:

select * from tSupplierProducts where [product] like "*" & forms!myForm!txtFind & "*"

[ArviLaanemets]

Let's assume you have a suppliers table p.e.
tblSuppliers: SupplierID, SupplierName, ...

Add another table
tblKeywords: KeywordID (Unique Autonumber Primary Key), SupplierID, Keyword
add an unique index for SupplierID+Keyword

On your Suppliers form, add a continuous subform with tblKeywords as RecordSource, link both forms through SupplierID.
On subform you have text boxes for KeywordID and SupplierID, with Visible property set to False, and a single-column combo box with ControlSource = tblKeyWords.Keyword, RowSourceType = Table/Query and RowSource = SELECT DISTINCT Keyword FROM tblKeyWords. Set property LimitToList to No.

Now, when you select any supplier, you can add keywords for this supplier in subform - you can select from list of existing keywords in tblKeywords, or you can enter new keyword when there is none passing entered before.

To get the list of suppliers having some specific keyword registered:
SELECT suppl.SupplierName FROM tblSuppliers suppl LEFT OUTER JOIN tblKeywords kwrd ON kwrd.SupplierID = suppl.SupplierID WHERE kwrd.KeyWord = 'YourSearchedKeyword' ORDER BY suppl.SupplierName

[orange]

You may get some ideas from this link in the Forums.


But the real question is "what stage are you at in this project?" If you are doing analysis and design to specify requirements, then gather all the facts. Are you dealing with Product names, or some attribute of a Product??
I think you need to identify the various (higher level categories of your Products) to assist with search, sort etc--but you know your environment and readers don't. Are you talking 100 Products or 10000 Products. Design will make a difference in ease of use.
I would suggest using a standardized keyword list(either build one to suit your needs OR use an existing code (UNSPSC etc). One that you have some control over or is managed y some authority.
As was suggested you could have a table that identifies which supplier or product has specific keywords. A standard list will help reduce typo and spelling errors, and will allow you to use indexes.

eg: Suppose
Apex Hardware is SupplerID 5600, and they supply Hammers,Glue, nails, and further that you have a standard Product terminology where
Hammer is 1
Glue is 23
Nails is 49

You could have a table tblSupplierSuppliesProduct with fields

SupplierID
ProductID
and records such as

5600,1
5600,23
5600,49

Each Supplier * Product combination is a separate record.

However, a lot depends on your context.

Nails is very broad (7" steel spike vs 1/2" stainless steel thumb tack vs 2 " ringed silicon-bronze....)
Hammer (claw, mallet, ball peen....) also very broad

You do not get the advantage of indexes with searches such as Like "*" & "string" & "*".

I think you need to identify your needs more completely before getting too deep into design- especially physical database.

Good luck.

[ntambomvu]

thank you all for the very detailed replies- i will go through then piecemeal and try them out before committing to any particular
solution
to answer a few questions
raman-- do you suggest then typing all the appropriate keywords into the text box for each supplier?

Avril- i like your suggestion and looks like this could offer a solution

Orange- there will likely be thousands of possibilities- so a standardized list will definitely be
an option to prevent typos ,duplications etc.
the context will generally be quite broad- like nails and screws would both be fasteners with sub category
nails . hammers will cover all types of hammers . likewise engineers will probably cover mechanical,electrical.construction - each having subcategories
mechanical industrial mechanical automotive etc etc.
so all further suggestions will be gratefully acknowledged.

[Micron]

You might want to consider a commodity based approach. Likely much more up front work when so many items are involved, but provides the most powerful & flexible interface, I think. You'd need a table for each commodity type as well as tables for each sub-type for a given commodity parent. The sub types are linked to the parent id. So Fasteners, Adhesives, Fluid Handling might be 3 first level types. Screws, nails, spikes, tacks... are children of fastener. Wood, metal, epoxies... are children of adhesives. Valves, pumps, tanks, hoses... are children of fluid handling. And so on. These tables contain description fields to guide the user in choosing the correct commodity codes when assembling the code for each part. A commodity table containing a link to each part ID contains the entire commodity code in it's separate fields (yes, this can result in many Null fields in some rows).

With this setup you can search for any part that meets all of the selected criteria chosen from the commodity combo boxes on a search form. Your search might be on

F	fastener
SC	screw
MA	machine
DIA	1/4" - 1/2"
LENGTH	.5" - 1.5"

Note that for 1/4" - 1/2", the actual commodity characteristic identifier would be the row id, not the string 1/4" - 1/2".
Can't recall if they worried about labels for the search form combos (the 3rd might be diameter for some parts, but not for others) or if they were just presented generically such as Level 1, Level 2, etc. This is how it was done where I worked, where I'm sure the parts table numbered in the millions since the commodity table identified if the part was in the plant stores system or was strictly supplier based. As I said, a likely a lot of work, but the more parts involved the more valuable it would become. You can index the commodity fields, but AFAIK, fields containing a composite set of keywords cannot be indexed.