Join Table

[Lost in Microbiology]

Hello All,

I am a total newbie, and have been tinkering with a publication database of
journal articles related to my work. I had created a table with title,
journal, year, links, and what it references (gram negative, gram positive,
etc.). I created a separate table for the authors, and made it a subform as a
single article can have multiple authos.

I created a query so I can search by author, or by title. My problem comes
when I search by title, as each title is repeated for every author. So a
single paper may be repeated 10 times (once for each author). I have the
tables linked by an auto-number called Publication ID, so each title has a
matching number to all authors attached to that article. (for example:
Publication Table, PubID 1 = "S.aureus in the MICU." In the Author Table,
PubID 1 = Johns, D.; PubID 1 = Smith, J.; PubID 1 = Harris, M).

Any thought on a query that would allow me to see the authors, title and
journal in one row? I think I am making it more complex than it needs to be.
I thought of creating a new table where PubID = each author, then linking
that table to the Publication table where the title and journal info are, but
just couldn't get it to work. I think I am missing one or two crucial steps.

Thanks for any help to give me that "click" moment.

 

[tina]

well, you have three entities here that i can see: journals (perhaps
publications is a better word), authors, and articles. oh, and probably a
fourth - topics (or perhaps categories).

you need to define how the entities are related, for instance:

one journal may have many articles, but each article is published in one
journal (i assume). this is a one-to-many relationship.
one article may have many authors, and one author may contribute to many
articles. this is a many-to-many relationship.
one article may be referenced under many topics (categories) - i'm guessing
here - and one topic may apply to many articles. this is a many-to-many
relationship.

based on the above analysis, i'd suggest the following structure, as

tblJournals
JournalID (primary key, Autonumber)
JournalName
<any other fields you need to list attributes that describe a journal, but
nothing else - nothing about specific issues of a journal, or articles in an
issue, etc.>

tblAuthors
AuthorID (pk, Autonumber)
FirstName
LastName
<any other fields you need to describe attributes of an author, date of
birth for instance, but nothing else - nothing about articles written by an
author, etc.>

tblTopics
TopicID (pk, Autonumber)
TopicName
<here's where you might have a record for "gram negative", and a record for
"gram positive"; it's simply a list of topics that the articles you enter
may address, so you can classify articles by the topic(s) they cover.>

tblArticles
ArticleID (pk, Autonumber)
ArticleName
JournalID (foreign key from tblJournals, Number data type, Long Integer
field size)
YearPublished
Link (if you only have one link for each article, this is the place to put
the field. but if one article may have more than one link, then the Link
field belongs in another table.)
TopicID (foreign key from tblTopics. note: if you want to classify each
article under only one topic, put the field here. but if you may want to
classify an article under more than one topic, then the TopicID field
belongs in a separate table.)

tblArticleAuthors
ArticleID (foreign key from tblArticles)
AuthorID (foreign key from tblAuthors)
<you can use these two fields as a combination primary key.>
each author will be entered for each article. so if one article has 8
authors, there will be 8 records in this table that alll have the same
ArticleID, but each will have a different AuthorID.

if you want to be able to assign more than one Topic to an article, then
you'd have a linking table for articles/topics, instead of the Topic field
in tblArticles, with the same structure you see in tblArticleAuthors above.
ditto Links.

the above is all basic relational design. recommend you read up on
relational design principles, so you'll understand why the setup is
important, and how to expand on it correctly. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[lise latulippe]

je suis francais ok

"Lost in Microbiology" <[email protected]> a
écrit dans le message de groupe de discussion :
(e-mail address removed)...

 

[Lost in Microbiology]

Hello Tina,

Thank you so much for the in-depth response. My question would now be, can I
alter the current structure of the database with all of the data present?
Your set-up is definitely much cleaner, I have the publications table which
contains the specific article titles, categories, publication date, and
actually an OLE link (pdf of article). Then I have the authors table, which I
made as a subform to the publication table.

There are over 300 entries, and I would hate to have to start from scratch.
But the relationships as you describe would require me to split a table. Is
that just a matter of exporting those fields to another table? I would then
add an autonumber field as the pk, and then link the tables using those,
correct?

Thanks again for all of your help. It is still a little daunting, but not as
scary as it was before.

 

[hor vannara]

"Lost in Microbiology" <[email protected]> a
écrit dans le message de
news:[email protected]...

 

[tina]

well, migrating data from one setup to another is almost always do-able, but
almost always a pain in the butt. with only 300-some records, i'd be tempted
to set up the structures, then do a series of dumps to populate the
supporting tables (tblJournals, tblAuthors, and tblTopics) and re-enter the
core data into tblArticles and tblArticleAuthors manually...but then i do
data entry for a living, so...

from what i can tell of your skill level, i don't think i want to try to
talk you through a migration in this forum. if you're using any version of
Access between A97 and A2003, you can send me a copy of your db if you want.
i'll set up the migration the way i'd do it if it was 3000 records instead
of 300, using Append and Update queries, and document what i'm doing, and
why, so you can learn from the db when i send it back to you.

if you want to do this, get my email address off my website, following the
directions for decoding the Example (you'll see what i mean). make sure you
*compact* the db before sending it, and zip it to under 1 MB if you can. oh,
and make sure you refer to the newsgroups in the Subject line, so i don't
delete your email as spam. i'll have time this coming weekend to look it
over. and you can find some time to study relational design, as i mentioned
before, so the migration process will make sense to you!

hth
http://home.att.net/~california.db/tips.html#aTip11