Keyword Index

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.










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

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

"Michael Gramelspacher" wrote:

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.

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

"Fred" wrote:

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.










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 Thu, 10 Jul 2008 13:49:03 -0700, GIraffe
wrote:

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.

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




"GIraffe" wrote:

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

"Fred" wrote:

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.










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 Thu, 10 Jul 2008 14:37:06 -0700, Fred wrote:

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


"GIraffe" wrote:

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

"Fred" wrote:

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.










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




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.

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

"Fred" wrote:

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




"GIraffe" wrote:

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

"Fred" wrote:

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.










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