If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Keyword Index
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. |
#13
|
|||
|
|||
Keyword Index
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 |
#14
|
|||
|
|||
Keyword Index
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. |
#15
|
|||
|
|||
Keyword Index
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 |
#16
|
|||
|
|||
Keyword Index
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. |
#17
|
|||
|
|||
Keyword Index
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 |
|
Thread Tools | |
Display Modes | |
|
|