Repeat queries of the same data

[slrphd]

I read a lot for work and the material is from many different disciplines. To keep track of all this information, I set up a simple db to keep track of all my references and have fields containing various bits of information for each record. In the beginning, I assigned one keyword to each reference as a means of retrieving articles on related subjects. Over time it has grown to three keywords per record although some of the keyword fields are null. All keywords are in a single, separate table and I add a keyword to a record from a pick-list. When I increased the number of keyword fields to three in tblCatalog, I just added more links to tblKeyword. Now, I am having a tough time trying to query tblCatalog. Obviously, my basic db organization is not right so I am looking for suggestions on how to straighten out this mess. Here is what I would like to do:

* During data entry, select form one to three keywords for each record
* During data retrieval, concatenate all the keywords (one, two or three) and
list the resulting text in a single control on a form.

This seems like it ought to be easy but not today. There must be something fundamental that I do not understand.

 

[andrew93]

Hi Steven
Have a read of this post - it discusses a very similar situation and allows you to keep your 3 keyword fields.
Andrew

 

[Pat Hartman]

In a 1-Many or Many-Many relationship "Many" simply means 0, 1, or more than 1. It does not mean 3 or 57 or 1096. If you have more than one of something, you have many - period! That means that you really need to use the correct table structure. Create a separate table to hold your keywords and watch your problems disappear. Use a subform to enter/view the keywords. Any other method will require a fair amount of code.