Keyword Index

[GIraffe]

Bottomline, I need to create a keyword index.

I have a file system that's nonuser friendly (system mandated by
organization, not my choice). It's hard to find anything in the system, so I
thought creating a database that would index by keyword or keywords what's in
a file so that I could find all my files, for example, that contain something
on "cats."

Originally, I had the database setup with one keyword box in a file table,
and I'd type in keywords or phrases, separated by a comma that would relate
to that file. Then if someone else or I needed a file, I could "search" my
form file by file until I found what we were looking for (will be horribly
timeconsuming when the database is larger then the 150 records I have now).
After searching this board, I see if would be more useful to setup a Keyword
Table and link it to my File Table.

I do not know how to set it up so that if I enter a new keyword it will
appear in the Keyword Table. Do I use a list box or combo box for my keyword
box in my file table? How do I choose more then one keyword or choose a
keyword and add a new keyword?

Once I create this Keyword table, I think I should be able to create a query
that'll sort by Keyword, then do a "group by" through the Report feature to
have all files that deal with "cats" too look like:

Cats
Breeds
Food
Vet Records

Here's my current File Table:

FileID [Autonumber]
FileCode [Text; Links w/ FileCode Table]
FileName [Text]
DispositionCode [Text; Links w/ DispositionCode Table]
Keywords [Memo]

I'd appreciate your thoughts, help or links to guides that'll help.

Thank you as always for your tremendous help, support, and compassion for
those of us that are not consistent Access users.

G

 

[Fred]

A quick note from Mr. low tech.

I do what you are doing a lot.

Try just changing your keyword field to a text field, stuff the relevant
keywords into there, and use a query to filter to the records that have your
keyword of interest. This runs fast

Fred

 

[GIraffe]

Hi Fred:

This will work for the interim and I thank you. Hopefully I can figure out
a way to get a compilation query (for all key words) so I can print it out
(ie, if I'm not around, how will anyone find anything in the files
otherwise?).

G

A quick note from Mr. low tech.

I do what you are doing a lot.

Try just changing your keyword field to a text field, stuff the relevant
keywords into there, and use a query to filter to the records that have your
keyword of interest. This runs fast

Fred

Bottomline, I need to create a keyword index.

I have a file system that's nonuser friendly (system mandated by
organization, not my choice). It's hard to find anything in the system, so I
thought creating a database that would index by keyword or keywords what's in
a file so that I could find all my files, for example, that contain something
on "cats."

Originally, I had the database setup with one keyword box in a file table,
and I'd type in keywords or phrases, separated by a comma that would relate
to that file. Then if someone else or I needed a file, I could "search" my
form file by file until I found what we were looking for (will be horribly
timeconsuming when the database is larger then the 150 records I have now).
After searching this board, I see if would be more useful to setup a Keyword
Table and link it to my File Table.

I do not know how to set it up so that if I enter a new keyword it will
appear in the Keyword Table. Do I use a list box or combo box for my keyword
box in my file table? How do I choose more then one keyword or choose a
keyword and add a new keyword?

Once I create this Keyword table, I think I should be able to create a query
that'll sort by Keyword, then do a "group by" through the Report feature to
have all files that deal with "cats" too look like:

Cats
Breeds
Food
Vet Records

Here's my current File Table:

FileID [Autonumber]
FileCode [Text; Links w/ FileCode Table]
FileName [Text]
DispositionCode [Text; Links w/ DispositionCode Table]
Keywords [Memo]

I'd appreciate your thoughts, help or links to guides that'll help.

Thank you as always for your tremendous help, support, and compassion for
those of us that are not consistent Access users.

G

 

[Fred]

Hello Giraffe,

Only about 10% of your original post talked about what you are really trying
to do (the other 90% was about your ideas on how to do it) and so I was
guessing. And that guess is for example, to show you all of the files where
"cats" was one of the entered keywords for them.

A simple direct way for you or them is to just enter "*cats*" in the
"criteria" space under the keywords field in the file table in the select
query design grid.

My strength is heavily using db's in real life appplications for 18 years.
My weakness is that most of my users work for me and so they can't slap me
upside the head to force me to make my make my db's even more friendly /
no-brainer. Other folks who answer these posts know coding far better than
me and can tell you how to do this is a more user friendly and bulletproof
way.

Hi Fred:

This will work for the interim and I thank you. Hopefully I can figure out
a way to get a compilation query (for all key words) so I can print it out
(ie, if I'm not around, how will anyone find anything in the files
otherwise?).

G

A quick note from Mr. low tech.

I do what you are doing a lot.

Try just changing your keyword field to a text field, stuff the relevant
keywords into there, and use a query to filter to the records that have your
keyword of interest. This runs fast

Fred

Bottomline, I need to create a keyword index.

I have a file system that's nonuser friendly (system mandated by
organization, not my choice). It's hard to find anything in the system, so I
thought creating a database that would index by keyword or keywords what's in
a file so that I could find all my files, for example, that contain something
on "cats."

Originally, I had the database setup with one keyword box in a file table,
and I'd type in keywords or phrases, separated by a comma that would relate
to that file. Then if someone else or I needed a file, I could "search" my
form file by file until I found what we were looking for (will be horribly
timeconsuming when the database is larger then the 150 records I have now).
After searching this board, I see if would be more useful to setup a Keyword
Table and link it to my File Table.

I do not know how to set it up so that if I enter a new keyword it will
appear in the Keyword Table. Do I use a list box or combo box for my keyword
box in my file table? How do I choose more then one keyword or choose a
keyword and add a new keyword?

Once I create this Keyword table, I think I should be able to create a query
that'll sort by Keyword, then do a "group by" through the Report feature to
have all files that deal with "cats" too look like:

Cats
Breeds
Food
Vet Records

Here's my current File Table:

FileID [Autonumber]
FileCode [Text; Links w/ FileCode Table]
FileName [Text]
DispositionCode [Text; Links w/ DispositionCode Table]
Keywords [Memo]

I'd appreciate your thoughts, help or links to guides that'll help.

Thank you as always for your tremendous help, support, and compassion for
those of us that are not consistent Access users.

G

 

[GIraffe]

Hi Fred:

Hmmmm, you are correct, I didn't mention the print version specifically
other then referencing the "group by" feature in Reports. Sorry I didn't
come over as clear as I thought I was.

Thank you for your time.

G

Hello Giraffe,

Only about 10% of your original post talked about what you are really trying
to do (the other 90% was about your ideas on how to do it) and so I was
guessing. And that guess is for example, to show you all of the files where
"cats" was one of the entered keywords for them.

A simple direct way for you or them is to just enter "*cats*" in the
"criteria" space under the keywords field in the file table in the select
query design grid.

My strength is heavily using db's in real life appplications for 18 years.
My weakness is that most of my users work for me and so they can't slap me
upside the head to force me to make my make my db's even more friendly /
no-brainer. Other folks who answer these posts know coding far better than
me and can tell you how to do this is a more user friendly and bulletproof
way.

Hi Fred:

This will work for the interim and I thank you. Hopefully I can figure out
a way to get a compilation query (for all key words) so I can print it out
(ie, if I'm not around, how will anyone find anything in the files
otherwise?).

G

A quick note from Mr. low tech.

I do what you are doing a lot.

Try just changing your keyword field to a text field, stuff the relevant
keywords into there, and use a query to filter to the records that have your
keyword of interest. This runs fast

Fred




:

Bottomline, I need to create a keyword index.

I have a file system that's nonuser friendly (system mandated by
organization, not my choice). It's hard to find anything in the system, so I
thought creating a database that would index by keyword or keywords what's in
a file so that I could find all my files, for example, that contain something
on "cats."

Originally, I had the database setup with one keyword box in a file table,
and I'd type in keywords or phrases, separated by a comma that would relate
to that file. Then if someone else or I needed a file, I could "search" my
form file by file until I found what we were looking for (will be horribly
timeconsuming when the database is larger then the 150 records I have now).
After searching this board, I see if would be more useful to setup a Keyword
Table and link it to my File Table.

I do not know how to set it up so that if I enter a new keyword it will
appear in the Keyword Table. Do I use a list box or combo box for my keyword
box in my file table? How do I choose more then one keyword or choose a
keyword and add a new keyword?

Once I create this Keyword table, I think I should be able to create a query
that'll sort by Keyword, then do a "group by" through the Report feature to
have all files that deal with "cats" too look like:

Cats
Breeds
Food
Vet Records

Here's my current File Table:

FileID [Autonumber]
FileCode [Text; Links w/ FileCode Table]
FileName [Text]
DispositionCode [Text; Links w/ DispositionCode Table]
Keywords [Memo]

I'd appreciate your thoughts, help or links to guides that'll help.

Thank you as always for your tremendous help, support, and compassion for
those of us that are not consistent Access users.

G

 

[Fred]

Hello Giraffe,

So your question will be:

1. Can a file have more than one keyword?

2. Is your report:

A. a list of files (i.e. each file appears once) or
B a list of keywords, with every file with that keyword underneath. (I.E.
if a file has 5 keywords it will be listed 5 times.


If "1" is "Yes" and 2 is "B" then my suggestion will not work and you need a
separate linked table which has a record for every instance of a keyword
being relevant to a file.

And, either way, a secondary table of keywords for dropdown lists etc.

 

[GIraffe]

Good Morning Fred:

Thank you for sticking this out with me. Your original suggestion (query
seeking particular keyword) works well when I'm here and I am able to access
the database (actually, it works much better then what I was doing which was
using the "find" in my form). Unfortunately, my officemates do not have
access to this database (nor would they use it if they did), so it would be
great if I could make a keyword index to print out so they can find things in
the files when I'm not here.

What I was hoping for is B of your reply, " list of keywords, with every
file with that keyword underneath. (I.E. if a file has 5 keywords it will
be listed 5 times). "

Doing a little more digging yesterday on this board, I think I have an
answer to my question. I think it is going to require VB code to do (I do
not know VB), so I'm thinking I'm out of luck.

What I was "hoping" for was to create a keyword table, populate it with a (I
now know) multiple-selection list box. Use a relationship of 1>many (1
keyword has many files), then write a query to pull the keywords out of the
keyword table and the files would be automatically listed accordingly. My
thinking, which I believe is now flawed, was that for each keyword I would
select or add in the list box, it would make each selection a separate row in
the keyword table, allowing me to do the query. I now believe that's not
possible (as I learned yesterday, even with multiple-selection, only one
value is saved in the table).

The bottom line, I think I'm out of luck. I do thank you for the query
suggestion (hadn't thought of that). That'll help a lot.

G

 

[Michael Gramelspacher]

What I was "hoping" for was to create a keyword table, populate it with a (I
now know) multiple-selection list box. Use a relationship of 1>many (1
keyword has many files), then write a query to pull the keywords out of the
keyword table and the files would be automatically listed accordingly. My
thinking, which I believe is now flawed, was that for each keyword I would
select or add in the list box, it would make each selection a separate row in
the keyword table, allowing me to do the query. I now believe that's not
possible (as I learned yesterday, even with multiple-selection, only one
value is saved in the table).

Does this express the general idea of what you want?

CREATE TABLE FileNames (
file_num INTEGER NOT NULL PRIMARY KEY,
file_name VARCHAR (255) NOT NULL);

CREATE TABLE Keywords (
file_num INTEGER NOT NULL
REFERENCES FileNames (file_num),
keyword VARCHAR (20) NOT NULL,
PRIMARY KEY (file_num, keyword));


INSERT INTO FileNames VALUES (1,'First File');
INSERT INTO FileNames VALUES (2,'Second File);

INSERT INTO KEywords VALUES (1, 'dogs');
INSERT INTO Keywords VALUES (1, 'goats');
INSERT INTO Keywords VALUES (1, 'horses');
INSERT INTO Keywords VALUES (2, 'pigs');
INSERT INTO Keywords VALUES (2, 'goats');

Keyword Report

Keyword Files
-------- ----
dogs First File
goats First File, Second File
horses First File
pigs Second File

 

[GIraffe]

Hi Michael:

I'm not sure Let me play with this a bit and get back to you.

Thank you for your thoughts.

D

 

[Michael Gramelspacher]

Hi Michael:

I'm not sure Let me play with this a bit and get back to you.

Thank you for your thoughts.

Just in case you need it:

Using my example files:

Create a form based on FileNames.
Create a continuous form based on Keywords.

Insert Keywords Subform into Filenames Form as a subform.

Access automatically links main to subform on file_num. Just type in your
keywords for each file name and they are automatically added to the Keywords
table.

Really, really simple.

Now grab this function from here and copy it to a general module:
http://www.mvps.org/access/modules/mdl0008.htm

Create this query as Query1 to look up the file_name:

SELECT Keywords.file_num,
FileNames.file_name,
Keywords.keyword
FROM FileNames
RIGHT JOIN Keywords
ON FileNames.file_num = Keywords.file_num;

This query uses Query1 and the fConcatFld function:

SELECT Keyword,
fConcatFld("Query1","Keyword","file_name","string",[Keyword]) AS Files
FROM Keywords
GROUP BY Keyword;

Keyword Files
dogs First File
goats First File; Second File
horses First File
pigs Second File

Print this and hand it to your people to find files by keyword.

 

[Fred]

Hello Giraffe,

I'm pretty good at analysis of the mission, underlying structure and
practical use. These other folks are 10 times better than me at
programming,. maybee it's a plus here that I'm lousy at that.

I'm still only guessing at some of the underlying mission.

With the right structure, to me the putative mission looks pretty simple
to solve with the right structure, an zero code. You actually had it in
your "junction table" except that your way of viewing / decribing misses /
hides the main points.

Three tables

"Files" table (PK = FileNumber) Has all of the data for eachfile except
keyword stuff.
this will have a lot of records, one for each file, with little automation
possible because it's the dtat that they are entering.

"Keywords" table List of keywords, one record each. PK = KeyWordNum
This table can't be that big (?) This is When there is a new keyword (a
keyword that has never been used before) they would just manually add it to
this list.

"FileKeyWordAssoc" Probably no PK needed. One record for each INSTANCE
of recording that a keyword from the list is relevant to the file. Just two
fields FileNumber and KeyWordNum This will be your largest table. You'll
want to automate creation of records on this as much as possible.

So, I'm guessing that you don't have a huge amount of keywords, but you have
a huge amount of use of keywords.

Link Files and FileKeyWordAssoc on the FileNumber fields.

Link FileKeyWordAssoc to Keywords on KeyWordNum

Make a file Form with a FileKeyWordAssoc subform, including a lookup to load
the KeyWordNum. In the subform it could also look up and show the KeyWord
associated with the KeyWordNum.

Your desired report is simply a FileKewWordAssoc report grouped by keyword,
with the keyword shown (only) in the group header. While the the details
section FileKeyWordAssoc, the content is actually your files, because you
will have it use the fiel table as a sort of lookup table, and hae it show
the fiel data that is associated with the "KeyWordNum" entry in the
FileKeyWordAssoc table.

 

[GIraffe]

Hi Michael:

Okay, I setup the 2 tables, added the keyword subform, and you are correct;
this was *really* easy to populate the keyword table (almost too easy ...
makes me wonder what I missed).

So I ran a few tests, went out of my form went back in and, uh oh, all the
keywords I had typed for a file were gone (they were still in the keyword
table, just gone off the form for the file). Unfortunately, I'm going to
need to see the keywords, so, just by looking at the form I'll know if I need
to add new ones, or delete a few.

I'm sorry ... This was one of those things that I didn't think about (ie,
seeing the keywords on my form). Any thoughts?

Thanks for your help.

G

Hi Michael:

I'm not sure Let me play with this a bit and get back to you.

Thank you for your thoughts.

Just in case you need it:

Using my example files:

Create a form based on FileNames.
Create a continuous form based on Keywords.

Insert Keywords Subform into Filenames Form as a subform.

Access automatically links main to subform on file_num. Just type in your
keywords for each file name and they are automatically added to the Keywords
table.

Really, really simple.

Now grab this function from here and copy it to a general module:
http://www.mvps.org/access/modules/mdl0008.htm

Create this query as Query1 to look up the file_name:

SELECT Keywords.file_num,
FileNames.file_name,
Keywords.keyword
FROM FileNames
RIGHT JOIN Keywords
ON FileNames.file_num = Keywords.file_num;

This query uses Query1 and the fConcatFld function:

SELECT Keyword,
fConcatFld("Query1","Keyword","file_name","string",[Keyword]) AS Files
FROM Keywords
GROUP BY Keyword;

Keyword Files
dogs First File
goats First File; Second File
horses First File
pigs Second File

Print this and hand it to your people to find files by keyword.

 

[GIraffe]

Hi Fred:

Thank you. I'm going to give this a shot. I've worked with junction tables
before and have run in to a *big* mess. But I'll take a deep breath and
relook at this. Possibly you have something where I won't need code (yes!),
I'm willing to give it a try.

Let me see if I can clarify my mission.

I have a filing system that is not user friendly (organization mandated, not
my choice). Finding things in the files is very difficult. So I thought
creating a database that would "crossfile" digitally (& in printed form) by
use of keywords what files contain what would help immensely. Examples of my
files:

100 - Breeds
200 - Medical
300 - Suppliers

I have papers in my 100 file on breeds of animals (German Shepherds,
Siamese, etc.); I have papers in my 200 file on all medical items (vets, vet
specialists, complementary medicine, drugs, etc.); I have papers in my 300
file on all suppliers (food, medical, bedding, etc.). These are broad
categories and would work for broad inquiries. However, I often get a
request for all the information I have concerning Siamese Cats ... period
(nothing more). My files could be several hundred, my keywords may be
several hundred or several thousand (I really won't know until I'm there).
My goal: To be able to print an index where my officemates could see all
files that involve cats or Siamese specific. The index will need to be (1)
printed so I can place it in front of the filing system, so that, in my
absence, they can find what they are looking for and (2) usable digitally,
which is the way I would find things. Even if my colleagues had access to my
database, they won't use it (like the paper route).

I hope that helps. I'll get back with you on the junction table route.

Thanks for sticking with me.

G

 

[Michael Gramelspacher]

Hi Michael:

Okay, I setup the 2 tables, added the keyword subform, and you are correct;
this was *really* easy to populate the keyword table (almost too easy ...
makes me wonder what I missed).

So I ran a few tests, went out of my form went back in and, uh oh, all the
keywords I had typed for a file were gone (they were still in the keyword
table, just gone off the form for the file). Unfortunately, I'm going to
need to see the keywords, so, just by looking at the form I'll know if I need
to add new ones, or delete a few.

I'm sorry ... This was one of those things that I didn't think about (ie,
seeing the keywords on my form). Any thoughts?

Thanks for your help.

G

A subform linked to a main form by Link Child Fields/Link Master Fields is not
going to not have the matching records in the subform. The link would be on the
form num. If the tables are set up right, then probably the subform is on a new
record, and you may need to scroll up to see the records already there. It works
for me here.

 

[Fred]

To: Giraffe

Great explanation.

I have a lot of experience in doing what you are trying to do. I think hat
the described structure will make that simple and do it.

A couple of extra notes:

A system with thousands of different keywords is going to be very laborious
and might die under it's own weight.

Make sure you enter useful description for the files. and a field that
automatically recorde the date the the record was created. I think that you
will find those very useful later for other search options.

I wouldn't totally give up the option of eventually making a user search
screen that is such a no-brainer to use that your users might like using it.
Then, for example, they could, in seconds, bring up all of the files that
have the word-of-interest in the description field, with no need to create a
keyword system.

Good luck!!!!

Fred

 

[Michael Gramelspacher]

To: Giraffe

Great explanation.

I have a lot of experience in doing what you are trying to do. I think hat
the described structure will make that simple and do it.

A couple of extra notes:

A system with thousands of different keywords is going to be very laborious
and might die under it's own weight.

Make sure you enter useful description for the files. and a field that
automatically recorde the date the the record was created. I think that you
will find those very useful later for other search options.

I wouldn't totally give up the option of eventually making a user search
screen that is such a no-brainer to use that your users might like using it.
Then, for example, they could, in seconds, bring up all of the files that
have the word-of-interest in the description field, with no need to create a
keyword system.

Good luck!!!!

Fred

Fred, I guess if the subform records could just not be there, then we could
just eliminate subforms from Access. Or maybe just rely on dynamic queries to
change the subform record source to match the main form values.

I like your suggestion about grouping on the keyword in the report. I guess it
could be a multiple-column report. This way would be nicer when the filenames
are longer. Maybe the concat function would be more appropriate for showing
just the file numbers.

Not having real-world experience works against me a lot, but I really like
Access and at times cannot restrain myself from jumping in when it might be
better not to.

 

[GIraffe]

Hi Fred:

Thank you for your point about a potential issue with so many keywords. You
are correct, I could have so many keywords it will "break" the system trying
print out an index whenever I need to add or subtract something. I am going
to have to re-visit this and give it more thought.

I really haven't "given up" per se on a user involved search. I am hopeful
that they'll come around. However, I have a reference library that I have in
a database with a search function (very easy to use), and they refuse to use
it. They'd rather go in a room, and spend 20 minutes looking for a
publication then spend 20 seconds doing the search on line and knowing
exactly where it is. I remain hopeful.

Once again, thank you for your points and help. If I decide to create the
index and need further help, I'll be back to the board.

G