Managing a glossary with multiple sources that apply to one term

[mikahm1]

Hi - I manage a glossary that is stored in Access. Each term can appear in
multiple publications. Currently, all of the sources are stored in one
field, however I am having problems running queries against the source field.
There are just over 1,000 terms, and each term can have anywhere from 1-15
sources. What is the best way to rework this database so that I can run
queries against the sources?

 

[KARL DEWEY]

Create a second table for Sources with at least two fields - TermID (long
integer) and Source (Text). You might want DateAdd (DateTime) and DateStop
(DateTime) to keep history when source added or removed.

Add an Autonumber field titled TermID to the Terms table as primary key.

Set a one-to-many relationship between Terms and Sources, selecting
Referential Integerity and Cascade Update.

Use a form/subform for Terms/Sources with Master/Child links on TermID. Use
a second form/subform for Sources/Terms.

You will need to parse the current Sources field to append records into the
new table.

 

[John W. Vinson]

Hi - I manage a glossary that is stored in Access. Each term can appear in
multiple publications. Currently, all of the sources are stored in one
field, however I am having problems running queries against the source field.
There are just over 1,000 terms, and each term can have anywhere from 1-15
sources. What is the best way to rework this database so that I can run
queries against the sources?

This would seem to be a classic many to many relationship: each publication
has many Terms and each term can appear in many Publications. This needs
*three* tables:

Publications
PublicationID Primary Key
<title, publication date, etc.>

Terms
Term <Text, Primary Key>

Glossary
PublicationID <link to Publications>
Term <link to Terms>
<any info about THIS term as it is used in THIS publication, e.g. "Normal"
in a publication about databases might have a different meaning than in a
psychological journal or a geometry text>