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

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 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
> 
>

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.

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

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