What you have here is a many-to-many relationship between Documents and
keywords as each document might have one or more keywords and each keyword
might refer to one of more documents. You'd star with two tables therefore:
Documents, which contains the path to the document in one column (the primary
key) in each row, along with any other columns such as a description of the
document etc; and keywords, which would simply contain each keyword as a
value in a column (the primary key) in each row.
A many-to-many relationship type is modelled by a third table
DocumentKeywords say with two foreign keys, each referencing the primary key
of the other two tables, so the table would have columns DocumentPath and
Keyword. The primary key of this table is a composite one made up of the
DocumentPath and Keyword columns. What this table does is resolve the
many-to-many relationship type into two one-to-many relationship types. When
creating these relationships make sure you enforce referential integrity and
cascade updates. This will mean (a) that only valid paths and keywords are
entered in DocumentKeywords and (b) that if a path or keyword is changed in
Documents and Keywords the changes will automatically be applied to any
matching rows in DocumentKeywords.
For entering keywords per document use a form based on the Documents table
and within it place a subform based on the DocumentKeywords table. The main
form would be in single form view and the subform in continuous form view and
they'd be linked on the DocumentPath fields. You'd just need one control in
the subform, a combo box bound to the Keyword field, with a RowSource of:
SELECT Keyword FROM keywords ORDER BY keyword;
You'd simply select a keyword from the list, entering as many rows as
necessary in the subform (one for each keyword for that document).
For finding documents with a particular keyword you can use a query with a
parameter so that the user can enter the keyword at runtime. Design the
query, including the Documents and DocumentKeywords tables, joined on
Document path. Include whatever columns you want from Documents and the
Keyword column from DocumentKeywords. In the 'ctriteria' row of the Keyword
column in query design view put:
[Enter keyword:]
The user will be prompted to enter the keyword when the query, or a form or
report based on it, is opened. There are ways in which multiple keywords can
be entered and documents which match either any or all of them returned, but
that's probably something to come back to later when you have the basic
functionality up and running.
Ken Sheridan
Stafford, England
