Many to Many Relationships

[brharrii]

I'm learning how to do Many to Many relationships and I think I've got the basic idea down, I am just not sure how to prevent the same 2 records from being associated twice.

For example:

My practice database deals with Authors and Books.

I was able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Upgrader's Guide to Microsoft Office System 2003".

I was also able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Automating Microsoft Access 2003 with VBA".

The thing I want to prevent is something like this:

Book:
- Automating Microsoft Access 2003 with VBA

Authors:
- Mike Gunderloy
- Susan Harkins
- Susan Harkins (Duplicate)

And vise versa

Thanks!

 

[jdraw]

I think you would have 3 tables
Authors
Books
BookAuthors --> This is a junction table ( research this)

Junction table ----resolving Many to Many relationships
http://www.foresightsoftware.com/AccessJunctionTables.htm

Note: Underlined signifies Primary key
Authors(AuthorID, FirstName, Lastname, other info about the Author)
Books(BookID,ISBN,Title,NmbrOfPages, other Book specific info)

BookAuthors(Id,BookId,AuthorId, any info specific to this Author/Book combo)

The dark red identifies a unique compound index to prevent duplicates

BookID is FK to Books
AuthorId is FK to Authors

 

[brharrii]

I should clarify, I have 3 tables setup, but I am still able to create duplicate instaces of the same Author - book combination for some reason.

I included a copy of the database in my original post if it helps to see it.

Thanks!

 

[jdraw]

I have acc 2003 so can NOT use an accdb format database.

If you read my post regarding the unique compound index, I think you will resolve duplicate issue.

When you describe 2 tables in your post, I expect you to have 2 tables. For future posts, I recommend you provide all relevant info to put your issue/problem/opportunity into context and to maximize communications with readers.

Good luck with your project.

 

[AccessMSSQL]

brharrli:
In your BookAuthors table if you have BookID, AuthorID make BookID and AuthorID both the primary key of the table. You can do this in design view of the table by highlighting both columns and then clicking the primary key icon. NOTE: If you already have bad data in the table (duplicates) you won't be able to do this. You must clean up the table of duplicates first.

 

[brharrii]

Perfect! That did Exactly what I was looking for, thanks!