Limiting Results in Query

[Lost in Microbiology]

I think I am ready to bang my head through my computer. Mostly because I over
analyze, and there is probably a simple solution.

I have a publication database I created. I have 2 tables. The contacts table
has four fields: Author ID (auto-number - pk), author_lastname,
author_firstname, and PublicationID (number)

The second table is my publication table with 15 fields, where PublicationID
is PK and is set to auto-number each entry. The rest of the fields are
journal, year, abstract, conclusion, supportive, etc.

When I run a Query to select the author, I am fine. However when I run a
query to select by Title, I get the same title repeated for each author, so
my table of 300 articles, looks like 3000 because each author is pulled. Is
there a way to write the query so it pulls each title and creates a separate
table to put the authors in one field?

Sorry if itisn't too clear, it's late and I am frustrated. Thanks for any
suggestions.

 

[Rob Parker]

Your problem arises because you have only two tables, and that's not
sufficient to handle the many-to-many relationship which exists between
authors and publications: each publication can have many authors, and each
author can have many publications.

You need what is commonly referred to as a "joining table" to deal with
this. The following article on Allen Browne's site will probably be
helpful: http://allenbrowne.com/casu-23.html

In essence, you need to remove the PublicationID field from the Authors
table, and store it, together with the AuthorID, in a new table for
AuthorPublication.

Once you've got the correct table structure, then you'll be able to design
queries which will give you what you want.

HTH,

Rob