Access 2007 Tutorial 1.3. Many to Many Relationship

In this video:
Limitations of One to Many Relationships
Association Tables
Deleting Relationships
Deleting Fields
Creating an Association Table
Creating a Query
Cartesian Products

Category:

Education

Tags:

• Limitations
• One
• to
• Many
• Relationships
• Association
• Tables
• Deleting
• Fields
• Creating
• Query
• Cartesian
• Products
• Access
• 2007
• Microsoft
• Office
• Relational
• Database
• Access 2010
• instructions
• tutorial
• howto
• tips & tricks

[pmsocho]

How do you prevent duplicate authorID-bookID entries. The model you are showing allow to add "author 1 - book 1" record and another "author 1 - book 1"record.

Am I right or am I missing something.

I would place 2 fields in Authored table - AuthorID and BookID and set the primary key on both of them.

Would such approach be good or not?

BTW, are you using Camtasia for recording :)

[djdates]

@pmsocho You are right, duplicate values could be problem in the authored table. In the 5.3. video in this series, SQL to detect and delete duplicate values is discussed. Your approach is one solution and would create a composite primary key. Another solution might be to create a composite unique constraint. Arguments for any approach would vary based on how you use the columns, performance implications, and the database you are using.

And yes, using Camtasia, Premiere, and After Effects.=)

[MrWatandoost]

plz answer my question why wouldt you just create a new record in books table by the name of authors instead of creating a new table. i get confused

[djdates]

@MrWatandoost If you add an author field to the book table, you would have to add duplicate book records when a book had two or more authors. Duplicate data increases the odds of errors creeping into our database. We might also be tempted to add multiple author fields, such as fields named Author1, Author2, Author3, etc. However, this would make it more difficult when we want to query and count the number of authors, plus we would have problems if we didn't create enough new author fields.

[peaches4free]

What do you mean when you call the new table "Authored"?

[djdates]

@peaches4free I'm not sure I understand your question. Authored is a verb meaning "to be the author of". One of the ways this concept is taught is by using verbs and nouns. Nouns are our tables, while verbs are our relationships. So with a one to many relationship, we have two nouns, book and author, and one verb, the authored relationship. With a many to many relationship, the authored relationship also includes a junction table as part of the many to many relationship. Hope this helps.

[TheLtUhura]

10 points because you included Neal Stephenson.

[mahjoobmirza]

I'm a beginner, where can i find complete tutorial for access 2010

[AlissaBrownify]

Beautiful tutorial. Nice book list :)

[adelgc]

very good job

thanks

[Notorious90000]

I have a problem, I have two tables, Table A has symptoms1, symptoms2, symptoms3, symptoms4, and Table B has a list of symptoms, I would like to make relationships to all other symptoms in Table A to Table B's Symptoms. How should I do it?????

[PiratesAndCowboys]

Looks NOTHING like the European version .. Ours looks more futuristic.. This was a bit of help tho thanks

[edesbalazs]

This is magic. Thank you!

[WennieBoy01]

Thanks! You speak clearly, and this video was very helpful.

[bestoOofriends]

why didn't you put both authorid and bookid in the new table as pk ?! when i do it it works !!

[karasik12]

Thx man !!!