Stop duplicate info from table A to table B

[SteveJtoo]

I have a database that lists all the books I have read. The Table "Books" holds "AuthorId' "Title" etc. I have another table that is called "Whats Next To Read" which I store the next book that I will read from that author. It also uses "AuthorId" and "Title" etc. In the form, how can I look to see if the title put in for that author in "Whats Next" does not have the same title already in "Books" table. I can use Dcount to find the title but it could be same title different author. I need same title with same author.

This is what I have so far.

If DCount("Title", "Books", "Title = '" & forms!Whatsnexttoread!Title & "' > 0 Then
Msgbox "Title is already in books.", vbInformation

End If

I need it to only look at the same author. Thanks for any help or ideas.

 

[MarkK]

In a database you keep track of things, and each thing has properties. In your case you keep track of books, and in respect to your purpose, whether or not you've read a book is a property of that book. So whether you've read a book or not should be a field in the book table, not a whole new table. That will solve your dupe name problem, and it means you never have to move data from table to table, you just change the value of the IsReadByMe field from False to True.
Makes sense?

 

[SteveJtoo]

I would do that except for two things.

1. I already have all forms etc and info done.
2. I want to show all authors (even the ones without a whats next flag. To me this seems much harder to do.

I would really like to keep it as is but with my original request of help.

 

[jdraw]

Have you researched normalization?
Have you defined/listed the facts that your database should support?

What Mark has suggested to you is basic database design. You say you have the forms and info already done-- did you test this with some sample data? If you scan most of the posts in this and other Access-related forums, you will see that many issues can be attributed to database design or a lack thereof.

Here's a tutorial on taking facts and designing a database -- including Normalization.