Create a many-to-many relations table

[thecity]

I am setting up a database in Access to query an author and the article(s) s/he has written.

Right now I have a table with one row of data containing a cell for the authors, the article name, the publication, etc. I need to create tables so that I can search by a field and retrieve the related information. For instancse, enter an author's name and retieve the articles written by that author. Or, enter an article's title and retrieve the authors, publication and related information.

Attached is an example. It's in html. I have entered the data in an Excel spreadsheet to be imported to Access.

I have an Author table and a Publication table in Access. I don't know how to link the two so that the correct author attaches to the correct article(s).

Thanks.

 

[fneily]

Better posted in:Microsoft: Access Tables and Relationships

Also some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You could have more then one author on an article.
You'd have three main tables:
tblAuthor
AutID Primary Key
Fname
Lname
Address
etc.

tblArticles
ArtID Primary Key
Name
Description
DateOfArticle
etc.

tblPublication
PubID Primary Key
Name

Now you must relate all the above tables through a junction table:
tblAARP (get it? Author, Article, Publish. An old man joke)
AARPID Primary Key
AutID Foreign Key
ArtID Foreign Key
PubID Foreign Key
DateOfPublication
Any other COMMON fields

Now everything just falls out through queries.

 

[thecity]

I'm with you on the AARP.

I set up the article table with Name=name of article; Decription=publication, etc.

I created the tblpub with the Pub=the publication name and the Name=the article name. I had trouble making the Pub the primary key. The pop up kept telling me that I couldn't do it because of of duplicates in the index. What am I doing wrong?
Thanks.

 

[fneily]

If you're new to Access, I'd suggest you buy at least three basic Access books and take some courses at your local community college before trying to create your own database.

AutID, ArtID, PubID are primary keys of their associated tables. They must be there. They assure no duplicate records. They can be autonumbers or you can create your own. An Example:
PubID PublicationName
MM Mad Magazine
SI Sports Illustrated
etc.

Article names do not go in the tblPublication table. They go in the tblArticles table. They are then associated in the junction table tblAARP.

If you haven't read the article I referenced, you should. It will start you understanding that Normalization, Table Relationships, primary keys, foreign keys are the very basis of tables. You can't do anything until you know this.

And I should have ArticleName instead of Name
PublicationName instead of Name

Access uses the word Name, so don't use it.