Keyword Index

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

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" wrote:

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

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

"Fred" wrote:

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" wrote:

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

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.




"GIraffe" wrote:

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

"Fred" wrote:

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" wrote:

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

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

"Fred" wrote:

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.




"GIraffe" wrote:

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

"Fred" wrote:

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" wrote:

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

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" wrote:

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

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 1many (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
"Fred" wrote:

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" wrote:

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

On Wed, 9 Jul 2008 06:37:01 -0700, GIraffe
wrote:

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 1many (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

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" wrote:

On Wed, 9 Jul 2008 06:37:01 -0700, GIraffe
wrote:

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 1many (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

On Wed, 9 Jul 2008 09:05:00 -0700, GIraffe
wrote:

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.