Want to use information from 2 tables to prevent duplicates from being enter on a form

[dreed64997]

I have a database for my book collection that I want to keep from entering duplicate book titles by the same author. The database has a book table that holds the Book Title and other information , author table that has fields for Last name and First name and a linking table. When I enter a book on my form I would like it to check to see if another book is already there with the same author and copyright year which is in the book table and then give me the option to either enter the book or not. I would also like it to show me the book information that is already in the database if that is possible.

I don't know if it can be done. Any help would be appreciated.

[ranman256]

if you key the table on these fields, you cant enter duplicates.

[dreed64997]

thanks for the reply. but it wont work. there can be books with the same name but different authors and vise versa.

[ssanfu]

I think you are on the right track..
It sounds like you have a Books table, an Authors table and a junction table that links Books and Authors (BooskAuthors). Don't know your form design, but it I would use VBA to do the checks you want.

[orange]

Further to Steve's comments, see this that may be useful with your linking/junction table.

[NoellaG]

Hi, in the books table I would put an unique index on the ISBN number. there can be duplicate titles, but all books have an unique ISBN number. If you can have the same book twice, use the structure tblBooks -> tblCopies where you can have several copies of 1 book.
The link tblBooks -> tblBooksAuthors <- tblAuthors remains unchanged.

[dreed64997]

Thanks NoellaG for the information. But even using the ISBN if a author changes publishers and that publisher reprints one or more of his books then that book would have a different ISBN. So I could still end up with duplicate book with the same name by the same author. That why I stated my question in the way that I did.