How to assign PK for two 2 tables in a RDBM?

[seb]

Quote Database
Narrative
For my research I need a Quote Database to storage Quotes form three references sources. Quotes can be collected from Reference Types such as Scientific Papers, PhD Thesis and MSc Thesis.

Reference information includes: Year (Number), Author (String), Title (String), ReferenceType (Text), Vol (String), Issue (String), Pages (Number), AccNumEN (Number).

ReferenceType information includes: MSc Thesis, PhD Thesis and, Scientific Paper.

Author information includes: Author Name (String).

Quote information includes: Quote (Memo)



I think the Entities are: Author, Reference, ReferenceType, Quote, and Keyword.
Having in mind the Entities, the relationships I already identified are:

An Author can have many References
A Reference can have many Authors

A Reference can have one ReferenceType
A ReferenceType can have many References

A Reference can have many Quotes
A Quote can have one Reference

A Quote can have many Keywords
A Keyword can have many Quotes

A Reference can have many Keywords
A Keyword can have many References

AccNumEN is the ID field in EndNote program for each Reference

PK for Refrence Type can be 1 for MSc Thesis, 2 for PhD Thesis and 3 for Scientific Paper
I don’t know how to assign PK to Author and PK to Reference.

Any light on this would be really appreciated.

[June7]

Consider tables:

tblAuthors
AuthID (PK)
LastName
FirstName
DOB
Gender

tblReferences
RefID (PK)
RefType
RefTitle
PubDate
Volume
Issue
Pages
AccNumEN

tblRefAuthors
RefID (FK)
AuthID (FK)

tblQuotes
QuoteID (PK)
RefID (FK)
Page

Can have a lookup table for the reference types or can manually build the list in combobox RowSource property.

The real complication here is how to handle the keywords. Options:

1. just a string of comma delimited characters in a text field
2. multi-value field
3. single table - I think only if the same keywords are used for reference and its related quotes
4. two tables - one for each tblReferences and tblQuotes

[ranman256]

I see these tables:
tAuthors
tAuthorRefs
tReferences
tRefQuotes

tQuotes

tRefTypes
----------
Quotes can be collected from Reference Types
3 Scientific Papers,
2 PhD Thesis
1 MSc Thesis.

Keywords table (useless)
(is this needed? Cant you search all words in the Reference or quote?)

[seb]

Hello June7
I will try with the 4 tables you recommend. Still I don't know how to assign PK for Authors and References Tables. First I will try with Autonumber
I am planning to query Quotes in a Form with a Textbox entering a Keyword. MS Access will display Quotes in a Form with a Listbox. When User clicks a line the Reference will be displayed in a Label. When user double clicks a line the Reference (.pdf) paper will be displayed. I know how to do these two steps.
I keep you inform.
Many thanks

[seb]

Hello ranman256
I think I need the Keywords table to Query Quotes in the References Table.
As I said to June7, I don't know how to assign Authors PK (canbe autonumbering?).
I let you know what I am doing with your help and June7 help.
Many thanks

[seb]

Hello June7 and ranman and Forum
Access 2010 in Spanish
I'm have been spending long hours designin my Quote Relational Database. In addition, I dont know how to include CoAuthors in the Database.
I have a Form to search Quotes using Keywords. However, with any Keyword, the Query returns all Quotes.
I attach the Quote_Test1 DB as example where I have to References (Scientific Paper and PhD Thesis).
I am looking a Query returnins fields:
Year-Author(s)-Titlte-RefType-Vol-Issue-AccNumEN-Quote(s)


June7. Please Could you explain me the meaning of DOB


Thank you very much. Each assistance is appreciated.

[June7]

DOB - date of birth

For co-authors - that's what my suggested tblRefAuthors is for. Multiple authors would be mean multiple records for each Reference.

[seb]

Hello. Many thanks for quick reply.
According to this Forum recommendations (I hope I understood) I already redesigned Database. I attached DB example.
However, when I enter a Keyword Query returns always 8 Quotes (already Total of Quotes). I need a Query to returns only Quotes related to Keyword. Any help is really appreciated.
My SQL is

SELECT tblReferences.PubDate, tblReferences.RefTitle, tblReferences.RefType, tblReferences.Volume, tblReferences.Issue, tblReferences.Pages, tblReferences.AccNumEN, tblKeywords.Keyword
FROM (tblReferences INNER JOIN (tblQuotes INNER JOIN (tblKeywords INNER JOIN tblKwQteRef ON tblKeywords.KwID = tblKwQteRef.KwID) ON tblQuotes.QuoteID = tblKwQteRef.QuoteID) ON tblReferences.RefID = tblKwQteRef.RefID) INNER JOIN (tblAuthors INNER JOIN tblRefAuthors ON tblAuthors.AuthorID = tblRefAuthors.AuthID) ON tblReferences.RefID = tblRefAuthors.RefID
WHERE (((tblKeywords.Keyword) Like [Forms]![Frm_Search].[Q_01_Kw_Qte] & "*"));

Note. In my SQL code [Forms] is written as [Formularios]
I can´t access to Authors's DBO. This field by the moment is useless in my DB.

Please Help

Cheer'

[June7]

The DOB field was only an example of data that could be associated with author. Retain whatever is of value to you.

[seb]

June7. Many thanks againg. I am thinking how to link DOB to CoAuthors.
I searched through the Access Forums and couldn't quite find what I was looking for my Db design.
Please let me know of any comments and/or suggestions you may have to facilitate the making of my Db design.